What's new
Fantasy Football - Footballguys Forums

This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

***Official*** Excel Help Corner (1 Viewer)

Ned

Ned

Footballguy
I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game for anything from formulas to Excel VBA.

I'll also try to bump this at times with various tips/tricks I've learned over the years. Here are a few random thoughts:

Repeating formulas

When you're using a formula against a data population that resides in multiple rows, you can make Excel do the work for you. First, enter the formula you want to use next to your data. While still selected on that cell with the formula, take your mouse and double click the tiny box that's in the lower right hand corner of the cell. Excel will autofill that formula all the way down until it finds a gap in your data.

Autofill Days, Months

Excel will autofill Days or Months for you. Enter "January" and then click/drag the tiny box in the lower right hand corner (while still selected on January) and drag it as far as you need it. Excel will fill the rest of the months. The same can be done for days of the week as well as the 3 letters for each (Jan, Feb, Mon, Tue). You can also add your own custom lists for Excel to autofill by going to File - Options - Advanced - Edit Custom Lists (this is the 2013 path).

Pivot tables are powerful

Learn how to use this tool - it will take care of a lot of things people are looking to do with formulas when they want to summarize/analyze it. Here's a decent video - https://www.youtube.com/watch?v=8ffdXfriLPQ

 
Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?

 
Harry Manback said:
Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?
Click to expand...
When you say stopped working what's it doing (or not doing)?

Any error messages?

Did you upgrade the file to 2010 (i.e. doing a save-as and saving it as a .xslx)?

 
Ned said:
Ignoramus said:
Jayrok said:
Ned said:
Jayrok said:
Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?
Click to expand...
=MID(G1,SEARCH("-?-",G1)-4,11)That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.
Click to expand...
This works for the row G1, but what about all the rows in G? Could I highlight the column then insert the function?

What I need is to run a function on the column and have the results (from the example above) be:

G1 -- 2014-A-0034

G2 -- 2011-B-1076

and so on for each row.
Click to expand...
Type the formula in G1. Copy G1 and paste all the way down as far as you need it. Excel is smart enough to update the references.
Click to expand...
:goodposting: Sorry, I assumed you knew that excel would do this part for you. Another way is to double click the tiny little box that shows up in the lower right hand corner of cell G1 (when selected). Excel will autofill that formula until it finds a break in the data (consecutive rows).
Click to expand...
Jayrok said:
Ignoramus said:
Jayrok said:
Ned,

I can't open that link, but I think I see where I misunderstood you in the beginning. I can paste the formula into a separate cell on the sheet and then drag the formula down the page, as you mentioned above, and have it correspond with the cells in the original column. And this works great.

G1-G56 (for instance) has the original data in each cell. Pasting your formula into H1 extracts the text I need from G1 and puts it into H1. When I drag the formula down column H it grabs all the text that I need from each row in column G. I can then go back and delete column G and use column H for my new data.

This works great. Thanks a million!
Click to expand...
:unsure: Hopefully you just omitted a step in your post.
Click to expand...
:doh: yes, of course. I can't delete column G or the formula is hosed. I can hide it instead.
Click to expand...
 
Last edited by a moderator:
Ned said:
Harry Manback said:
Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?
Click to expand...
When you say stopped working what's it doing (or not doing)?

Any error messages?

Did you upgrade the file to 2010 (i.e. doing a save-as and saving it as a .xslx)?
Click to expand...
Yes, all of those things I tried.

No errors, the excel file almost appears to be treating the drop down as a picture, not a dynamic drop down box.

 
Actually, when checking for compatibility I get the following:

Uninitialized ActiveX controls cannot be transferred to the selected file format. The controls will be lost if you continue.

 
Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?

 
Harry Manback said:
Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?
Click to expand...
Harry - we had a few macros go kaput recently and had to do this fix for activex:

1) Shut down all office applications you're running - locally or via Citrix

2) Paste this this link into your browser: http://support.microsoft.com/kb/3025036/EN-US

3) Scroll down a bit and click the microsoft "fix it" button and follow the prompt

Note there are two "Fix it" button on that page - click the first button

If you are prompted, click "Save" and then go into your Downloads folder and open the file from there and follow the prompts

 
Jayrock, you could also just use Data --> Text to Columns and tell it to start a new column where it finds the "#" sign.

Then delete the columns you don't need and do it again on the "," if you have too much data (as in the 2nd record you posted).

 
A couple random thoughts of mine:

While probably basic for most, the options under View / Window are helpful ...freezing frames to keep titles in place, and different ways to align workbooks when working with two or more ..OR to open a new window of the same workbook to view different areas of the workbook. Under Formulas, the Watch Window is a neat tool to track a specific cell value(s) as other changes are being made.

For a long time, I was unaware of the separate Developer tab that needs to be enabled under File / Options / Customize Ribbon. You can then insert some great Control tools for user selections.

--

A set of excellent support books, available for less than $20 each on Amazon, are the Excel 2013 Essential Skills (and Expert Skills) with the Smart Method. Every lesson is two pages with detailed steps and clear screen shots. The supporting files for training are available from the related web site for free.

ned, I look forward to reading more about how you and other advanced users utilize Excel.

 
I keep shortcuts in my toolbar for paste values, paste formulas, paste format, freeze panes, filter, group, ungroup, and couple others like Paste and Transpose that really make life easy. I also use ctrl + 1 a lot for formatting and stuff.

 
TheWalkmen said:
I keep shortcuts in my toolbar for paste values, paste formulas, paste format, freeze panes, filter, group, ungroup, and couple others like Paste and Transpose that really make life easy. I also use ctrl + 1 a lot for formatting and stuff.
Click to expand...
Good point. A well-organized Quick Access Toolbar with some Separators is very useful.

 
Notorious T.R.E. said:
Harry Manback said:
Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?
Click to expand...
Harry - we had a few macros go kaput recently and had to do this fix for activex:

1) Shut down all office applications you're running - locally or via Citrix

2) Paste this this link into your browser: http://support.microsoft.com/kb/3025036/EN-US

3) Scroll down a bit and click the microsoft "fix it" button and follow the prompt

Note there are two "Fix it" button on that page - click the first button

If you are prompted, click "Save" and then go into your Downloads folder and open the file from there and follow the prompts
Click to expand...
This did it, unfortunately I don't know if it's a fix I can have our external vendors use.

Thanks!

 
There was a recent Microsoft Office update that screwed up Active X Controls for all existing spreadsheets. i create a lot of models using VBA at work and Im the one who discovered the solution at work even though Im not in IT. You have to delete the MSForms.exd file

 
Oh man... I'm going to use you like the prettiest man in prison. I'll probably be checking in here weekly.

 
How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.

 
sports_fan said:
How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.
Click to expand...
get yourself a VBA book. Its easy and people look at you like a God.
 
Ned said:
TheWalkmen said:
Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?
Click to expand...
Try using MATCH instead. It will return the row it was found. I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php
Click to expand...
much quicker processing time.
 
sports_fan said:
How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.
Click to expand...
I was able to make better sense of the vba book stuff after recording some simple macros and then messing with the code. Made it easy to connect what I did with the code. I still suck but I use a few mainly for formatting or automating a redundant task
 
sports_fan said:
How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.
Click to expand...
I'm self taught and the bolded is how I learned along with a lot of online reading. There are more efficient ways of doing things vs. How the macro is recorded. A few random tips: Indent your code on things like If/Then/Else. It makes your code much easier to read. Use four spaces or a single tab.

Code: 
If Range("B1") = "Hi Shuke" Then    Range("C1") = "69"Else    Range("C1") = "0"End If
There's rarely ever a need to select a cell. Instead of this:
Code: 
Range("A1").SelectSelection.CopyRange("B1").SelectActive sheet.Paste
Do this:
Code: 
Range("A1").Copy Destination:= Range("B1")
Use the With statement whenever you can. Instead of this:
Code: 
Range("B1").Value = "Woz sucks"Range("B1").Bold = True
Do this:
Code: 
With Range("B1")    .Value = "Woz Sucks"    .Bold = TrueEnd With
Use vbNullString when you need a blank instead of "".
 
bushdocda said:
sports_fan said:
How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.
Click to expand...
I was able to make better sense of the vba book stuff after recording some simple macros and then messing with the code. Made it easy to connect what I did with the code. I still suck but I use a few mainly for formatting or automating a redundant task
Click to expand...
:thumbup: There are a ton of books out there that will give you everything you'll ever need, but I'm not a book guy. I learn quicker by just diving in and feeling my way through it.

 
Last edited by a moderator:
Ned said:
Autofill Days, Months

Excel will autofill Days or Months for you. Enter "January" and then click/drag the tiny box in the lower right hand corner (while still selected on January) and drag it as far as you need it. Excel will fill the rest of the months. The same can be done for days of the week as well as the 3 letters for each (Jan, Feb, Mon, Tue). You can also add your own custom lists for Excel to autofill by going to File - Options - Advanced - Edit Custom Lists (this is the 2013 path).
Click to expand...
I seem to have lost the box when we switched to 2010. Any idea how to re-enable it? Have never been able to find an option anywhere.

 
Macro question:

I have spreadsheets with about 15 columns that I have to format each month with borders and put in subtotals for the last 4-5 columns of the sheet (and do a custom border for the subtotals).

The sheets vary in the number of rows, so I do not know how to tell it which row is the final row where it should be subtotaling and custom bordering. Thoughts?

 
Long Ball Larry said:
Ned said:
Autofill Days, Months

Excel will autofill Days or Months for you. Enter "January" and then click/drag the tiny box in the lower right hand corner (while still selected on January) and drag it as far as you need it. Excel will fill the rest of the months. The same can be done for days of the week as well as the 3 letters for each (Jan, Feb, Mon, Tue). You can also add your own custom lists for Excel to autofill by going to File - Options - Advanced - Edit Custom Lists (this is the 2013 path).
Click to expand...
I seem to have lost the box when we switched to 2010. Any idea how to re-enable it? Have never been able to find an option anywhere.
Click to expand...
I'm on my iPad so I can't check for sure, but it's under excel options - editing options. Called drag and drop or something along those lines.
 
Long Ball Larry said:
Macro question:

I have spreadsheets with about 15 columns that I have to format each month with borders and put in subtotals for the last 4-5 columns of the sheet (and do a custom border for the subtotals).

The sheets vary in the number of rows, so I do not know how to tell it which row is the final row where it should be subtotaling and custom bordering. Thoughts?
Click to expand...
LastRow = Range("a1048576").end(xlup).row
 
shadyridr said:
Ned said:
TheWalkmen said:
Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?
Click to expand...
Try using MATCH instead. It will return the row it was found.I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php
Click to expand...
much quicker processing time.
Click to expand...
With an index/match the index group, the entire match row, and the entire match column are highlighted, not the actual cell with the returned value. I'm not sure it can be done.

 
Last edited by a moderator:
TheWalkmen said:
shadyridr said:
Ned said:
TheWalkmen said:
Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?
Click to expand...
Try using MATCH instead. It will return the row it was found.I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php
Click to expand...
much quicker processing time.
Click to expand...
With an index/match the index group, the entire match row, and the entire match column are highlighted, not the actual cell with the returned value. I'm not sure it can be done.
Click to expand...
I misread that last night and didn't realize you were using the actual Trace Precedents function. Yes, I don't think there's a way to highlight the actual resulting matched cell. I'm not sure exactly what you're doing without seeing your sheet, but you can get the address of a matched cell pretty easily via formula...

Here's a google sheet of what I think you're asking for (TheWalkmen tab) - https://docs.google.com/spreadsheets/d/1CX39meSEa2uwFAmh_rgn4x5ltS-7zdzYncfSjpT1tL8/edit?usp=sharing


Column A is the data to search through

Column D is the search criteria

Column E is the matched address

 
I feel like this should be really easy but I just cant figure it out.

I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.

For example:

===|===A===|===B===|===C===|===D===|

=1=|BLA====|=======|BLA====|=======|

=2=|IAII=====|IAlI=====|=======|=======|

=3=|12A====|=======|=======|12A====|

 
Last edited by a moderator:
Not 100% sure how it handles character values, but you could try the "max" function for the entire range of cells. If there's only one value there I think it would be returned.

 
For text values (non-numeric):

If you start in A1, use the below formula:

=INDEX(B1:IU1,0,MATCH(REPT("z",255),B1:IU1))

This uses INDEX-MATCH to scan for the last text value in a given range. You can alter B1:IU1 to suit your range.

 
Last edited by a moderator:
wdcrob said:
Not 100% sure how it handles character values, but you could try the "max" function for the entire range of cells. If there's only one value there I think it would be returned.
Click to expand...
If you are finding numerical values this is the correct answer imo, but it will return 0 if there are only text values in the columns.

Starting in A1:

=MAX(B1:IU1)

 
Last edited by a moderator:
hagmania said:
For text values (non-numeric):

If you start in A1, use the below formula:

=INDEX(B1:IU1,0,MATCH(REPT("z",255),B1:IU1))

This uses INDEX-MATCH to scan for the last text value in a given range. You can alter B1:IU1 to suit your range.
Click to expand...
Thanks man... you're awesome.

 
In cell a1 type...

=hlookup("*",b1:iu1,1,0)

Copy down in column a.

Lookups return the first value they find and in this case it is looking for any values with the wildcard character.

 
Last edited by a moderator:
hagmania said:
For text values (non-numeric):

If you start in A1, use the below formula:

=INDEX(B1:IU1,0,MATCH(REPT("z",255),B1:IU1))

This uses INDEX-MATCH to scan for the last text value in a given range. You can alter B1:IU1 to suit your range.
Click to expand...
I used offset-match, which is very similar. Also, rather than specify a "high value" with rept("z",255), used the -1 option on the match, which is "greater than or equal" and it takes a low value

=OFFSET(A14,0,MATCH("0",B14:D14,-1))

 
Ned said:
Long Ball Larry said:
Macro question:

I have spreadsheets with about 15 columns that I have to format each month with borders and put in subtotals for the last 4-5 columns of the sheet (and do a custom border for the subtotals).

The sheets vary in the number of rows, so I do not know how to tell it which row is the final row where it should be subtotaling and custom bordering. Thoughts?
Click to expand...
LastRow = Range("a1048576").end(xlup).row
Click to expand...
A large number of my macros have a

LastRow = .Cells(Rows.count, 1).End(xlUp).Row

somewhere in them.

 
Looking for a little assistance please.

=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))

this line works great if there is info to find if not it returns an #NA

For some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?

 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
2K
culdeus
culdeus
krista4
Middle-aged Dummies are back and bursting at the "themes" to get going! Full theme ahead!
101 102 103
Replies
5K
Views
89K
higgins
higgins
pollardsvision
Broadband Facts Label for cell and internet service
Replies
3
Views
449
pollardsvision
pollardsvision

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top