• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Excel syntax question

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Excel syntax question

    I have a work sheet that uses a formula in multiple places to refer to another sheet in the same work book to fetch data from the second sheet. That sheet I'm working on needs to be added to other workbooks to perform the same function but with differently named worksheets as the data source.

    I cannot for the life of me figure out the syntax to be able to put the target worksheet name in a cell and refer to that in the formula without breaking it. Formula is

    =IF(A15="","",(INDEX(C26,MATCH(A15,'DATA SOURCE'!$F$4:$F$100,0),9)))

    I've tried INDIRECT etc but just can't get the syntax right.

    DATA SOURCE is the bit I need to make generic so the only change needed to add the sheet to other workbooks is to change one cell rather than a dozen formulas.

    Any suggestions?
    "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

    #2
    does Excel formula: Dynamic worksheet reference | Exceljet help or have you already been there.

    merely at clientco for the entertainment

    Comment


      #3
      So the sheet you're working on is going to be copied into each workbook as an extra sheet?

      One way to create the table of sheet names that eek suggests is to do something like this in a hidden section of the sheet you're working on and reference that table in your INDIRECT formula:
      https://www.howtoexcel.org/tips-and-...k-without-vba/

      Comment


        #4
        Originally posted by eek View Post
        does Excel formula: Dynamic worksheet reference | Exceljet help or have you already been there.
        It did thanks, I'd overlooked the need for concatenation. Didn't need a table just a single reference from one cell to do it.

        =IF(A15="","",INDEX(INDIRECT("'"&$A$1&"'!$A$4:$BM$ 100"),MATCH(A15,INDIRECT("'"&$A$1&"'!$F$4:$F$100") ,0),10))

        "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

        Comment

        Working...
        X