Excel 2007 : Showing the contents of a cell from a different sheet that changes position.

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

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.

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

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

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.