Forum Discussion
bostonsullivan
Mar 03, 2019Copper Contributor
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 AmairahSilver 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
- bostonsullivanCopper Contributor
Thats perfect! thanks so much!