Referencing worksheet name in formula on different sheets

Topic

New Reply

We have a multi-sheet costing and quotation tool in Excel (2007 and later). One of the sheets contains a table of model numbers and prices exported from SAP on a regular basis. This sheet is named “SAP04222012” or whatever the date was when the export occurred.

At various places throughout other sheets in the workbook, specifically on the quotation page, we make reference to this price list sheet in a formula such as =UPPER(VLOOKUP(C129,SAP04222012!$A$3:$E$4778,2,0)) where C129 contains the product part number. This formula will return the model description in all upper case. Others across the line will return list price and cost price accordingly.

However, every time we update the SAP sheet we need to do a global search and replace for the sheet name. It occurs to me that there should be a way to reference the SAP sheet name in the formula so whenever the sheet name changes the references are automatically updated. I’ve looked at =CELL(“filename”) and =INDIRECT but can’t really make sense of it nor work out how to use them in these formulae.

Can someone help?

Keith

Reply | Quote

Replies

You could put the sheet name in A1 and then use A1 (or some other cell) to indicate the range.
A1: SAP04222012!$A$3:$E$4778

Then use for the formula:
=UPPER(VLOOKUP(C129,Indirect($A$1),2,0))

You can change the sheet name and range in A1, and all the formulas will use the new range.

SteveSteve

Reply | Quote

Thanks Steve, but if I understand it, the INDIRECT function only references a cell on the active sheet. I need to be able to reference across different sheets. Instead of the string from the tab name (i.e. SAP04222012) I need a VARIABLE that can be referenced in other formulas and that will change when the tab name is changed.

In other words Dim SAP_TAB as string
SAP_TAB = MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)

and then in formulas ON OTHER SHEETS, =UPPER(VLOOKUP(C129,SAP_TAB!$A$3:$E$4778,2,0))

so that when I change the tab name of the SAP sheet, say to SAP05012012, every formula throughout the workbook that references that sheet for price data will not error when it can’t find the old sheet that has been replaced.

Keith

Reply | Quote

Indirect works with cells on a different sheet. You are giving it the sheet reference to find it. The cell (A1) in the example is the “variable” and when that variable changes, the formulas will recalculate. You can put a formula into A1 if desired. Your formula will work in a cell, but it will only give the sheet name of the cell of the active sheet. If you want to get the sheet name of the new SAP sheet with a formula like that, the “A1″ part needs to have an explicit reference to a cell on the SAP sheet, and thus you will always have to change this reference when you add a new SAP sheet. So perhaps I am missing the question…

Steve
PS why not just paste the NEW SAP sheet over the old one and then rename it? Renaming the sheet will automatically adjust all the formulas…

PPS instead of my original post you can also use:
A1: SAP04222012

Then use for the formula:
=UPPER(VLOOKUP(C129,Indirect($A$1&”!$A$3:$E$4778″),2,0))
Which is essentially what you seem to want

Reply | Quote

Got it – This works.

Many thanks.

Reply | Quote