Forum Discussion

Ahmadnoori's avatar
Ahmadnoori
Copper Contributor
Feb 22, 2023

referencing a cell

My summary sheet makes a table for the profits, costs, etc. of my company for every year. So there are sheets for 2010-2022 all of which have the following format: 2022:          A                 ...
excel
Formulas and Functions
  • Ahmadnoori's avatar
    Ahmadnoori
    Copper Contributor
    Feb 23, 2023

    HansVogelaar first of all, thank you for your reply ! This works but the issue with it is that it relies on Profits being the first row after February which may not always be the case in the future. Is there anyway to adjust the code to take this into consideration ? I know I could just change the +1 accordingly but I want something more efficient. Thanks again! 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 23, 2023

      Ahmadnoori 

      Here is a formula that will work in Excel in Microsoft 365, Office 2021 and online (in the browser):

       

      =LET(m, "March", t, "Profit", p, MATCH(m, '2022'!$A:$A, 0), r, INDEX('2022'!$A:$A, p+1):INDEX('2022'!$A:$A, p+1000), q, MATCH(t, r, 0), INDEX('2022'!$B:$B, p+q))

       

      Instead of the literal strings "March" and "Profit", you can use cell references, for example if A2 contains March and B2 contains Profit:

       

      =LET(m, A2, t, "P2, p, MATCH(m, '2022'!$A:$A, 0), r, INDEX('2022'!$A:$A, p+1):INDEX('2022'!$A:$A, p+1000), q, MATCH(t, r, 0), INDEX('2022'!$B:$B, p+q))

      • Ahmadnoori's avatar
        Ahmadnoori
        Copper Contributor
        Feb 23, 2023

        HansVogelaar this is perfect! Thank you so much !! It works but I’m not quite sure why. Can you please explain the code / your thought process ? I would appreciate it very much ! Thanks again 🙂

Resources