Forum Discussion

denamark1's avatar
denamark1
Copper Contributor
Nov 28, 2022

Change parts of a formula using data from another cell

Formula: =-SUMIFS('0493'!$F$2:$F$10000,'0493'!$C$2:$C$10000,"*" & $A7 & "*",'0493'!$A$2:$A$10000,">="&DATE(2022,2,1),'0493'!$A$2:$A$10000,"<"&DATE(2022,3,1))

---------------------------

In the above formula which will be located in each cell row of column B, I want the formula to automatically substitute the 0493 with the text on the same row as in column A.

 

Example when formula is in column B, row 1:

=-SUMIFS('0801'!$F$2:$F$10000,'0801'!$C$2:$C$10000,"*" & $A7 & "*",'0801'!$A$2:$A$10000,">="&DATE(2022,2,1),'0801'!$A$2:$A$10000,"<"&DATE(2022,3,1))

============================= 

     A        B          C

1  0801

2  0493

3  3361

4  8801

5  9212

Excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 28, 2022

    denamark1 

    Try this one:

    =-SUMIFS(INDIRECT(A1&"!F2:F10000"),INDIRECT(A1&"!C2:C10000"),"*" & $A7 & "*",INDIRECT(A1&"!A2:A10000"),">="&DATE(2022,2,1),INDIRECT(A1&"!A2:A10000"),"<"&DATE(2022,3,1))

Resources