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                    B    

1: January

2: Profit                10, 000

3: Costs                  5, 000

4: February

5: Profit                20, 000

6: Cost                   8,000

.

.

 

I know that if I wanted to refer to the cell B5 (profit for February) in my summary sheet, I could do ='2022'!$B5 but I don't want to do this because in the future, profit may be in the 3rd row or 4th row etc. 

 

Is there any way to reference the cell so that it still gives me the profits for February regardless of what row it moves to ? 

 

I tried to use a match function with "Profit" as the lookup_value but I had trouble figuring out how to specify the profit for a specific month. I came up with this:

INDIRECT(MATCH("Profit", INDIRECT(CONCATENATE("A", MATCH("February", A1:100, 0))):A100, 0))

 

The idea is that I am looking for "Profit" but only within a specific interval which is given by INDIRECT(CONCATENATE("A", MATCH("February", A1:100, 0)) which spits out A4. So the function simplifies to INDIRECT(CONCATENATE("B",MATCH("Profit", A4:A100, 0))) which works and gives me 20,000 but only when I use it in the 2022 sheet.

 

The issue with this is that I don't know how to use this in my summary sheet. I tired to do ='2022'!CONCATENATE("B", MATCH("February", '2022'!A1:100, 0)) but this doesn't work.

 

Any help would be greatly appreciated!

 

 

excel
Formulas and Functions

6 Replies

Sort By
    • 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))

Resources