|
Can you not accomplish that with conditional formatting?
|
#
?
Apr 13, 2016 22:27
|
|
|
|
| # ? Apr 25, 2024 02:06 |
|
|
PRADA SLUT posted:I'm looking for a VB script that will search a single column for a entry or entries (by name), and if it finds it, apply some format to the cell (font color or highlight it). It's easy enough to do in VBA, but just use the auto filter, search for butt, and go wild.
|
|
#
?
Apr 13, 2016 23:01
|
|
|
Xandu posted:Can you not accomplish that with conditional formatting? Yes, but the idea is that I want this script to be able to work with numerous different documents, and I'd like to have a few different scripts with different search terms that can be used.
|
|
#
?
Apr 13, 2016 23:15
|
|
|
That's not VB Script, that's Conditional Formatting. It's literally a button you click. After you figure out that, learn to record a macro and then use a form to link to said macro. You still don't need to touch VB script. If you're fancy you can use a dropdown but I'm pretty sure all that poo poo's already in Tables and Pivot Tables so you're just reinventing the wheel instead of learning to drive the car. coyo7e fucked around with this message at 23:58 on Apr 13, 2016 |
|
#
?
Apr 13, 2016 23:56
|
|
|
Later I want it to Do Things based on this, like copy the information to a new spreadsheet and format it, so the VB scripting is necessary. This is just step one and I'm working out the rest as the needs of the project change.
|
|
#
?
Apr 14, 2016 01:20
|
|
|
Here, I think this should work.code:As a side note to Excel Professionals, I'm completely self-taught so if this is garbage in some way please let me know.
|
|
#
?
Apr 14, 2016 09:34
|
|
|
So I'm trying to create a score spreadsheet for my league in Madden Mobile and have most of it figured out but not sure if it's possible to do the following things. 1) When my league plays a new tournament, I add a new column to the front of our scores so you can view most recent scores first (behind averages and last 7 tournament averages). Is there a way to automatically lock which columns are used in formulas? Right now when I add a new column the formula used to calculate averages will move with it and not include the new one. For example, latest tourney is in Column D and averages are totaled from column D - column Z, but when I add new column in, the formula shifts from column D to column E. Is there a way to fix that? 2) When inputting scores, there are 2 ways to get a zero. 1 is if you actually don't score any points (duh) and the 2nd is if you don't take your drives and a 0 is put in. Both ways need to be utilized to score averages, but I also want to track how many times people skip as a separate stat/column. Is that possible? Is there a way to enter zero two different ways to it can be tracked and used for 2 different formulas? It's on Google Sheets so here is the link in case anyone wants to take a look around. https://docs.google.com/spreadsheets/d/1Qt_l7xrvzuiH-N8YEfZ_cP93kf3znIBuBMhJV-TH2Cs/edit?usp=sharing
|
|
#
?
Apr 18, 2016 14:10
|
|
|
The answer to #1 is to put a dollar sign in front of the bit you want to fix. So it would becode:code:I haven't experimented with #2 so I'm not sure, but you could maybe come up with something that uses the difference between a cell containing "0" and a blank cell?
|
|
#
?
Apr 18, 2016 14:39
|
|
|
potatocubed posted:The answer to #1 is to put a dollar sign in front of the bit you want to fix. So it would be Thanks but when I did that and added a new column, it went from $D2:Z2 to $E2:AA2 still 2nd thing would work if I could somehow tell Google Sheets that a blank cell = 0 for certain formulas (column B & C), and to count total blank cells on row for column D? Then have a normal 0 count towards column B & C formulas but not count on column D.
|
|
#
?
Apr 18, 2016 14:55
|
|
|
There's got to be a better way to do this, but if you wanted to use D for didn't take drives, an array formula would work. =ArrayFormula(AVERAGE(IF(D1:Z1="D",0,D1:Z1))) Leaving it blank works too of course, just change "D" to "".
|
|
#
?
Apr 18, 2016 15:00
|
|
|
Max Peck posted:There's got to be a better way to do this, but if you wanted to use D for didn't take drives, an array formula would work. No clue what that gave me when I put it in. Can't figure out where it's pulling those numbers from and what it's averaging.
|
|
#
?
Apr 18, 2016 15:07
|
|
|
Alfalfa posted:No clue what that gave me when I put it in. Can't figure out where it's pulling those numbers from and what it's averaging. Change the Zs to whatever is actually the last tournament right now (T?), it's counting all the blanks as 0s just like the formula says to.
|
|
#
?
Apr 18, 2016 15:11
|
|
|
Max Peck posted:Change the Zs to whatever is actually the last tournament right now (T?), it's counting all the blanks as 0s just like the formula says to. Duh lol. Changed it to sum as well since I wanted to total number of blank cells but it's just giving me sum of all the numbers in that range.
|
|
#
?
Apr 18, 2016 15:14
|
|
|
Alfalfa posted:Duh lol. =COUNTBLANK(E1:T1)
|
|
#
?
Apr 18, 2016 15:19
|
|
|
Max Peck posted:=COUNTBLANK(E1:T1) Awesome that works. What do I need to tweak now to column B&C to count blanks as 0's towards those averages as well? Thanks for all the help. I'm trying to learn Excel/Sheets more in-depth and this spreadsheet is my practice document, but I suck lol.
|
|
#
?
Apr 18, 2016 15:30
|
|
|
Alfalfa posted:Awesome that works. What do I need to tweak now to column B&C to count blanks as 0's towards those averages as well? Just adjust the array formula above to capture the appropriate columns, so =ArrayFormula(AVERAGE(IF(E1:T1="",0,E1:T1))) and =ArrayFormula(AVERAGE(IF(E1:K1="",0,E1:K1))).
|
|
#
?
Apr 18, 2016 15:35
|
|
|
Max Peck posted:Just adjust the array formula above to capture the appropriate columns, so =ArrayFormula(AVERAGE(IF(E1:T1="",0,E1:T1))) and =ArrayFormula(AVERAGE(IF(E1:K1="",0,E1:K1))). I believe that works perfectly. back to my first question now. When I change formula E2:T2 to $E2:T2 and then add new column on E, formula will move to $F2:U2 still. Alfalfa fucked around with this message at 15:47 on Apr 18, 2016 |
|
#
?
Apr 18, 2016 15:44
|
|
|
Alfalfa posted:I believe that works perfectly. It's a bit hacky, but you can 1) Change your fomula to D2:T2 2) Put something blank or arbitrary in column D that won't impact your formulas 2) Hide Column D 3) When you insert columns, insert before column E The hard part is step 2, and making sure that whatever you put in column D won't affect your formulas.
|
|
#
?
Apr 18, 2016 16:33
|
|
|
similar situation to the OP, try to fake it till I make it, got a pretty good position open I'm aiming for to get my food in the door and I'm getting this as some poo poo that'll be expected of me: "taking PDFs; converting them to Excel spreadsheets; then parsing up the data into a format we need. The use of advanced scripts and calculations speeds this up far more than copying and pasting." I've got some Excel/VBA knowledge but I'm kinda lost. I throw myself at your feet, goons, because gently caress if I know how to use "advanced scripts" and "calculations" to speed that up.
|
|
#
?
Apr 18, 2016 17:51
|
|
|
TheFallenEvincar posted:similar situation to the OP, try to fake it till I make it, got a pretty good position open I'm aiming for to get my food in the door and I'm getting this as some poo poo that'll be expected of me: Just from my personal experience - PDF->Excel is a road paved with pain and frustration. Admittedly I never attempted to automate it but even getting well formatted, correct, all inclusive data from a PDF once has been horrible. The only good news was I talked to one of our vendors they advised it was just as easy to provide me with .xlsx as a .pdf.
|
|
#
?
Apr 18, 2016 21:21
|
|
|
TheFallenEvincar posted:similar situation to the OP, try to fake it till I make it, got a pretty good position open I'm aiming for to get my food in the door and I'm getting this as some poo poo that'll be expected of me: You could probably use VBA/VBScript to click the right buttons for you in Adobe Acrobat Pro to do this, and then open up the exported worksheet, and then do whatever you need to do to the used ranges. Still likely to be tons of errors that will need a human eye for review. To be honest, it sounds like temp data entry work, but with a small slice of bonus of BA / dev work. Bonus here meaning still working at temp data entry wages and not enough work to throw on your resume afterwards.
|
|
#
?
Apr 18, 2016 21:52
|
|
|
A bit of Googling turned up a way to use VBA to directly vacuum up data from pdfs, but I've never tried it so I have no idea how well it works (if it works).
|
|
#
?
Apr 18, 2016 22:27
|
|
|
I've imported a few small tables from PDFs into Excel and my impression is that it's gotten a lot better over time, but yeah it still sounds like a nightmare. If the PDFs are directly saved from Excel/Word/whatever and not printed out, scanned, and uploaded then you'll probably have a pretty low error rate, at least. I hope you're importing data with totals/subtotals/averages or something else that you can use to help you check the data because otherwise get ready to be doing glorified temp data entry. (If you can get them to spring for a second monitor it's not so bad and if you get really good at 10-key data entry then it'll go by painlessly enough.)
|
|
#
?
Apr 19, 2016 13:15
|
|
|
I want to see what happens if you just left-click select the whole pdf document and paste it into excel. What horrible mess will appear into excel? Maybe it is possible to read this mess. =IF(A1=0,"",A1) dragon drop
|
|
#
?
Apr 21, 2016 03:50
|
|
|
=iferror(a1, "uh error apparently, for some reason") when you copy and paste from pdf, you might be able to "Find and Replace" several different weird characters that you can then exclude ... If you really have a large amount of data in Excel that you received from some other file type: I can help you. A big ugly pdf file that you left-click select copy paste... PASTE this file into cell A1. What does that look like? (I have a lot of experience with VBA and I love to teach) Cool Bear fucked around with this message at 05:49 on Apr 21, 2016 |
|
#
?
Apr 21, 2016 03:55
|
|
|
uh sorry about that. I only post when I'm drunk. It's really rude of me, I'm sorry for being annoying.
|
|
#
?
Apr 23, 2016 02:46
|
|
|
For you VBA wizards out there, do you use other languages for problems that don't necessarily need a spreadsheet? For instance, I need to loop through a directory of folders and pull out csv files to combine into a master CSV, then do some parsing. Typically I'd use Python but it seems VBA can accomplish just about anything with all the reference libraries available to it. The feeling I get from reading various forums is that a lot of people use VBA as the last option.
|
|
#
?
May 15, 2016 02:13
|
|
|
Hughmoris posted:For you VBA wizards out there, do you use other languages for problems that don't necessarily need a spreadsheet? For instance, I need to loop through a directory of folders and pull out csv files to combine into a master CSV, then do some parsing. Typically I'd use Python but it seems VBA can accomplish just about anything with all the reference libraries available to it. The feeling I get from reading various forums is that a lot of people use VBA as the last option. Pretty sure the libraries you're talking about are not specific to VBA and could be used from most any language (COM libraries and whatnot; the world of "native" VBA libraries is basically non-existant afaik). I wouldn't recommend anyone use VBA for anything unless you are already using it for related things (I have a massive legacy VBA code base to maintain and extend). If this is just a simple task that you're going to do once and throw away, it doesn't really matter what you use. I think for tiny tasks like combining/filtering text files most normal people use unix command line tools like awk or whatever.
|
|
#
?
May 15, 2016 02:58
|
|
|
VBA is best used to slightly extend the functionality of Office products. If normal, out of the box use of Excel or Word or Access, etc isn't 95% of your solution, chances are you have better options out there. The problem is that you can do a lot more with VBA than you should and it's very easy to wind up with Excel being nothing more than a lovely front end to a ubiquitous, "easy" script engine.
|
|
#
?
May 16, 2016 22:50
|
|
|
ShimaTetsuo posted:Pretty sure the libraries you're talking about are not specific to VBA and could be used from most any language (COM libraries and whatnot; the world of "native" VBA libraries is basically non-existant afaik). I wouldn't recommend anyone use VBA for anything unless you are already using it for related things (I have a massive legacy VBA code base to maintain and extend). If this is just a simple task that you're going to do once and throw away, it doesn't really matter what you use. fosborb posted:VBA is best used to slightly extend the functionality of Office products. If normal, out of the box use of Excel or Word or Access, etc isn't 95% of your solution, chances are you have better options out there. Thanks, that's about what I expected. I've accomplished a few neat macros with VBA a little while ago out of necessity and was just curious if I want to fall further down that rabbit hole. I don't think I do.
|
|
#
?
May 18, 2016 03:09
|
|
|
fosborb posted:The problem is that you can do a lot more with VBA than you should and it's very easy to wind up with Excel being nothing more than a lovely front end to a ubiquitous, "easy" script engine. Try as hard as you can to avoid this, otherwise you end up with 'spreadsheets' that have 6k lines of VBA macros doing what should've been done in C# or python or something with a lot less headache.
|
|
#
?
May 21, 2016 04:36
|
|
|
In the spirit of spending an inordinate amount of time writing VBA code to accomplish tasks that could be written in more transferable languages for my own benefit...any recommendations to make my sheet protection / VBA passwords hard to crack? VBA has its place. The speed of output and transparency that VBA can be incredibly useful in business-analyst roles for sharing quick data with serious non-coders.
|
|
|
#
?
May 24, 2016 06:19
|
|
|
Bit9 + Websense or similar across the enterprise to block unauthorized programs from cracking your spreadsheet. For VBA, compile in VS and install as a COM add-on. No bit length is really going to save you here. Excel on its own is not appropriate to safe guard anything truly sensitive. You have to secure the surrounding environment.
|
|
#
?
May 24, 2016 06:36
|
|
|
Dumb excel/google sheets question incoming... On this spreadsheet - http://bit.ly/momentum-scores Is there a way to set it so the names under column A can change cell color based on the letter listed in column K? For example, if I change the letter in K2 from A to B, I would like cell A2 to change from blue to purple automatically.
|
|
#
?
May 24, 2016 16:00
|
|
|
You should be able to do that with conditional formatting. Phone posting here, or else I'd have more details, but look up conditional formatting on the Googles.
|
|
#
?
May 24, 2016 16:02
|
|
|
ScarletBrother posted:You should be able to do that with conditional formatting. Phone posting here, or else I'd have more details, but look up conditional formatting on the Googles. Yah I've used conditional formatting for the colors in general, but I'm not sure how to tell Google how to change a cell that isn't in the range of cells it is checking the data in.
|
|
#
?
May 24, 2016 16:16
|
|
|
 Try something like that. Sorry for the picture of a screenshot. I'm at work.
|
|
#
?
May 24, 2016 16:24
|
|
|
Alfalfa posted:Yah I've used conditional formatting for the colors in general, but I'm not sure how to tell Google how to change a cell that isn't in the range of cells it is checking the data in. Highlight a single cell in Column A, say A2. Go into Conditional Formatting, add a new rule, and select "Use A Formula To Determine Which Cells To Format". Your formula should be something like =$K2="A". Set up similar rules for each value. Then you can extend the range to which the rule applies either by copying the formatting down your column or by editing the range under "Applies to" in Manage Rules.
|
|
#
?
May 24, 2016 16:25
|
|
|
Awesome thanks. This was the forumla that ended up working under Custom Formula Range: A2:A66 =($K2:$K66)="A"
|
|
#
?
May 24, 2016 16:42
|
|
|
|
| # ? Apr 25, 2024 02:06 |
|
|
I've been handed a unique problem with some excel spreadsheets. I have stack of spreadsheets that are identical in layout, but the data changes each day. They are all named in the same convention: Production MMDDYYYY.xlsx and the data is always in the Run Schedule tab Each workbook has several lines representing a job that has a Task (Column E) and Total Time (Column L) among other information, but those are the two I'm looking for. I'm looking for a way to get the Total Times for all workbooks. The trouble I'm having is the reference to the other workbooks and basing it off a variable. For example, I can pull out each one with ='[Production 01012016.xlsx]Run Schedule'!L169 but I don't want to have to do that for every single day. What I would like would be a universal formula for ='[Production <B1>.xlsx]Run Schedule'!L169 and I can change B1 around to pull from different workbooks. EDIT: Looks like INDIRECT() is the function to use. I'm using =INDIRECT("'[Production " & B1 & ".xlsx]Run Schedule'!L5") to concatenate the file name based on the date value in B1. However, I am only able to do this for open files. When I try to add "\\server\folder\" before "[Production" I get a reference error. I've tried mapping the folder to a Z: drive and doing a "'Z:\Production"... but to no avail. Walking through the evaluation steps gets me to =INDIRECT("'Z:\[Production 01012016.xlsx]Run Schedule'!L5 which looks correct, but shoots back a #REF! raej fucked around with this message at 20:20 on Jun 1, 2016 |
|
#
?
Jun 1, 2016 17:37
|
|
