Forum Discussion

jmcintos's avatar
jmcintos
Copper Contributor
Apr 21, 2025

Look Up for multiple sheets?

I wonder if someone would be so kind as to help me with a little problem...  I have 12 different workbooks, one for each store, for labour and sales forecasting. On each workbook there are a few d...
Formulas and Functions
jmcintos's avatar
jmcintos
Copper Contributor
Apr 21, 2025

Thank you so much for your reply, your help is so gratefully received! I fear I may be making a mistake... probably an elementary one. 

Whereby the other workbook is called 'Purley', the sheet in question is called 'Quarterly Summary' the range is 'A1:Q31'. When I enter the below formula in my summary workbook it just produces a 0 in A1

=LET(range,'[Purley.xlsx]Quarterly Summary'!A1:Q31,IF(range=A1, Q31, range))

 

 

HansVogelaar's avatar
HansVogelaar
MVP
Apr 23, 2025

Try

=LET(range,'[Purley.xlsx]Quarterly Summary'!A1:Q31,IF(range="", "", range))

  • jmcintos's avatar
    jmcintos
    Copper Contributor
    Apr 28, 2025

    You are nothing short of a genius! Thank you so much!

    One final question - I don't suppose there's any way to bring across the formatting too? 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 29, 2025

      Formulas only return values, they don't "return" formatting. That would require either conditional formatting or VBA code.

Resources