I have a spreadsheet that takes data from the other 52 spreadsheets and totals it.

The columns in the sheet correspond to the names of the other spreadsheets (using dates, i.e., 01/01/2017 column contains data from 01012017 spreadsheet). If I change the column names to match the spreadsheet names, how do I use them in the VLOOKUP, so I can just copy my formulas and not have to update the formula in each column?

My VLOOKUP formula is this: =IFERROR(VLOOKUP($A2,'12302016'!$A$7:$I$22,9,FALSE),0)

How do I change the '12302016' to use the column name?

Screenshot of my data

I know that I cannot put 12/30/2016 as my spreadsheet name.

  • 1
    Have a look on INDIRECT function. Probably that's what you need – Máté Juhász Nov 1 '16 at 19:59
  • Could you share a screenshot of what your data looks like? I'm having trouble understanding what you need from the prompt, but I agree with @MátéJuhász that you'll probably want to use INDIRECT – airstrike Nov 1 '16 at 21:55
  • @MátéJuhász, thank you. I, also, added a screenshot. – DataGirl Dec 22 '16 at 20:50

Instead of '12302016'!$A$7:$I$22 use:

INDIRECT("'"&SUBSTITUTE (TEXT(E$1,"dd/mm/yyyy),"/","")&"'!$A$7:$I$22")

Where E$1 is your header.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.