Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    San Jose, California, USA
    Posts
    82
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Referencing worksheet name in formula on different sheets

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    San Jose, California, USA
    Posts
    82
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Last edited by sdckapr; 2012-04-24 at 18:28.

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    San Jose, California, USA
    Posts
    82
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Got it - This works.

    Many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •