SOLVED
Home

Using Cell Values to Reference another sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-359566%22%20slang%3D%22en-US%22%3EUsing%20Cell%20Values%20to%20Reference%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359566%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20an%20amateur%20at%20excel%2C%20and%20it's%20quite%20possible%20that%20what%20I'm%20asking%20has%20a%20simple%20fix%2C%20or%20no%20fix%20at%20all.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20making%20a%20budget%20for%20myself%20and%20my%20wife.%20Each%20months%20respective%20budget%20is%20on%20its%20own%20sheet%2C%20and%20I%20have%20a%20separate%20stats%20sheet%20for%20calculations%20-%20which%20is%20what%20you%20will%20see%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20do%20is%20have%20a%20running%20data%20set%20for%20each%20month's%20respective%20spending%20on%20different%20accounts.%20What%20you%20see%20in%20the%20first%20image%20below%20is%20a%20formula%20I%20am%20using%20to%20input%20data%20into%20the%20table%20from%20different%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20second%20image%20you%20will%20see%20what%20I%20am%20trying%20to%20do%2C%20more%20or%20less.%20I%20want%20to%20use%20the%20cells%20in%20column%20I%20to%20reference%20their%20respective%20sheets%20-%20that%20way%20I%20dont%20have%20to%20retype%20the%20same%20formula%2012%20times.%20The%20formula%20you%20see%20in%20the%20second%20image%2C%20obviously%2C%20doesn't%20work.%20It%20just%20returns%20a%20text%20string%2C%20therefore%20creating%20an%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84946i61199C4B0F1BB3DD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-03-03%20at%203.48.17%20PM.png%22%20title%3D%22Screen%20Shot%202019-03-03%20at%203.48.17%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84947i7676001149B19A79%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-03-03%20at%203.50.22%20PM.png%22%20title%3D%22Screen%20Shot%202019-03-03%20at%203.50.22%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-359566%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360143%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Cell%20Values%20to%20Reference%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360143%22%20slang%3D%22en-US%22%3E%3CP%3EThats%20perfect!%20thanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359604%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Cell%20Values%20to%20Reference%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359604%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Findirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EINDIRECT%3C%2FA%3E%20function%20to%20do%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20formula%20in%20cell%20J4%20and%20drag%20it%20down.%3C%2FP%3E%3CPRE%3E%3DSUMIF(INDIRECT(I4%26amp%3B%22!%24AK%247%3A%24AK%2423%22)%2C%22%26lt%3B%26gt%3BEnding%20Balance%22%2CINDIRECT(I4%26amp%3B%22!%24AM%247%3A%24AM%2423%22))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
bostonsullivan
New Contributor

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. 

Screen Shot 2019-03-03 at 3.48.17 PM.pngScreen Shot 2019-03-03 at 3.50.22 PM.png

2 Replies
Solution

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

 

Thats perfect! thanks so much!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies