Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

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

  • Like 3

Share this post


Link to post
Share on other sites

:blackdot: Maybe we can move or copy our exchange from the other thread to here.

  • Like 1

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

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)?

Share this post


Link to post
Share on other sites

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)?

=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.

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.

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.
: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).

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!

:unsure:

Hopefully you just omitted a step in your post.

:doh: yes, of course. I can't delete column G or the formula is hosed. I can hide it instead.

Edited by Ned

Share this post


Link to post
Share on other sites

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?

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)?

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 - 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

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Good point. A well-organized Quick Access Toolbar with some Separators is very useful.

Share this post


Link to post
Share on other sites

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 - 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

This did it, unfortunately I don't know if it's a fix I can have our external vendors use.

Thanks!

  • Like 2

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

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

Share this post


Link to post
Share on other sites

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.

get yourself a VBA book. Its easy and people look at you like a God.
  • Like 1

Share this post


Link to post
Share on other sites

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?

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

much quicker processing time.
  • Like 1

Share this post


Link to post
Share on other sites

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.

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

Share this post


Link to post
Share on other sites

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.

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.

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:

Range("A1").SelectSelection.CopyRange("B1").SelectActive sheet.Paste
Do this:

Range("A1").Copy Destination:= Range("B1")
Use the With statement whenever you can. Instead of this:

Range("B1").Value = "Woz sucks"Range("B1").Bold = True
Do this:

With Range("B1")    .Value = "Woz Sucks"    .Bold = TrueEnd With
Use vbNullString when you need a blank instead of "".

Share this post


Link to post
Share on other sites

. Its easy and people look at you like a God.

Ain't that the truth.

Share this post


Link to post
Share on other sites

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.

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
: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.

Edited by Ned

Share this post


Link to post
Share on other sites

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).

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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).

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.

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.

Share this post


Link to post
Share on other sites

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?

LastRow = Range("a1048576").end(xlup).row

Share this post


Link to post
Share on other sites

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?

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

much quicker processing time.

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.

Edited by TheWalkmen

Share this post


Link to post
Share on other sites

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?

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

much quicker processing time.
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.

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

Share this post


Link to post
Share on other sites

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====|

Edited by Kanil

Share this post


Link to post
Share on other sites

Think youre gonna have to write some code for that using instr() function

Share this post


Link to post
Share on other sites

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====|

The nasty way is to concat everything.

In A1, enter '=B1&C1&D1&E1&F1&G1&H1&....' etc. all the way to IU1. If all the other cells are empty, then you'll be left with just the text appearing in the 1 cell that has anything in it.

There's probably a better way. This doesn't work in my Excel, but it might in yours: http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/ For whatever reason, when I F9 I get the actual values rather than a text of the range, which would make it much easier to type out.

Edited by Walking Boot

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Edited by hagmania
  • Like 2

Share this post


Link to post
Share on other sites

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.

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)

Edited by hagmania

Share this post


Link to post
Share on other sites

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.

Thanks man... you're awesome.

Share this post


Link to post
Share on other sites

Is this where excel nerds come to hang out?

Share this post


Link to post
Share on other sites

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.

Edited by NetnautX

Share this post


Link to post
Share on other sites

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.

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))

Share this post


Link to post
Share on other sites

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?

LastRow = Range("a1048576").end(xlup).row

A large number of my macros have a

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

somewhere in them.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.