• 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!
Collapse

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

  • You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
  • You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
  • If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Previously on "Excel syntax question"

Collapse

  • DaveB
    replied
    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))

    Leave a comment:


  • ladymuck
    replied
    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/

    Leave a comment:


  • eek
    replied
    does Excel formula: Dynamic worksheet reference | Exceljet help or have you already been there.

    Leave a comment:


  • DaveB
    started a topic Excel syntax question

    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?

Working...
X