Forum Discussion

bostonsullivan's avatar
bostonsullivan
Copper Contributor
Mar 03, 2019
Solved

Using Cell Values to Reference another sheet

Hi, 

 

I am an amateur at excel, and it's quite possible that what I'm asking has a simple fix, or no fix at all. 

 

I am currently making a budget for myself and my wife. Each months respective budget is on its own sheet, and I have a separate stats sheet for calculations - which is what you will see below. 

 

What I am trying to do is have a running data set for each month's respective spending on different accounts. What you see in the first image below is a formula I am using to input data into the table from different sheets. 

 

In the second image you will see what I am trying to do, more or less. I want to use the cells in column I to reference their respective sheets - that way I dont have to retype the same formula 12 times. The formula you see in the second image, obviously, doesn't work. It just returns a text string, therefore creating an error. 

  • Hi,

     

    You need the INDIRECT function to do that.

     

    Please try this formula in cell J4 and drag it down.

    =SUMIF(INDIRECT(I4&"!$AK$7:$AK$23"),"<>Ending Balance",INDIRECT(I4&"!$AM$7:$AM$23"))

     

    Hope that helps

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi,

     

    You need the INDIRECT function to do that.

     

    Please try this formula in cell J4 and drag it down.

    =SUMIF(INDIRECT(I4&"!$AK$7:$AK$23"),"<>Ending Balance",INDIRECT(I4&"!$AM$7:$AM$23"))

     

    Hope that helps

     

Resources