I export a weekly report that lists Sales tax information for that week into an excel workbook. That workbook has 5 sheets. On the first sheet of this workbook I consolidate the information I've exported to other sheets within this workbook. Basically, I want the total sales tax number from each sheet (week). The problem is that number is not in a static cell on each sheet or from week to week.
Is there a way to find the cell which has the total sales tax number in it if, that cell differs from week to week?
thank for the help on this one.
tjamestx
Last edited by tjamestx; 02-07-2012 at 03:28 PM.
Yes, certainly. This is typically done by finding something consistent that is always nearby, perhaps the cell to the left always says "Total Sales Tax", or perhaps it is always the last cell with data in it in a particular column.
If you need help determining what the best method would be, post up your workbook and point out the cells you're trying to dynamically "spot" on the five sheets. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JBeaucaire
First, thank you for the reply. I've attached my workbook, I'm not familiar enough with all the functions within Excel to know what might be best in this situation. Any help would be greatly appreciated.
tjamestxSTR.xlsx
Yes, I was right, the cell you want is always in column H and always on the same line as the word "TOTAL" in column A. So a straight INDEX/MATCH will find it.
But to make it really cool so only one formula is needed to copy down to collect data from different sheets, we put that into an INDIRECT() function to build the formula based on sheet names. I've changed your layout to include a sheetname on each row, then the formulas in B:C use that sheetname to create a formula. So the same formula in row 3 is just copied down...
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JBeaucaire, you're a genius! I kinda figured a work around using CELL+VLOOKUP but, this works so much better and will make my life easier. Thanks again
tjamestx
Last edited by tjamestx; 02-07-2012 at 02:45 PM.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks