Home

Excel question

%3CLINGO-SUB%20id%3D%22lingo-sub-282020%22%20slang%3D%22en-US%22%3EExcel%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282020%22%20slang%3D%22en-US%22%3E%3CP%3ECurrently%20using%20excel%202013%20-%20my%20question%20(or%20what%20I%20want%20to%20do)%20is%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20have%20a%20sheet%20(format%20that%20is%20printed%20out)%20but%20the%20information%20on%20that%20sheet%20is%20taken%20from%20a%20different%20tab%20-%20based%20on%20a%20value.%20For%20example%3A%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20with%20Tabs%20Labeled%20DataSheet1%20to%20DataSheet20%20-%20I%20want%20to%20be%20able%20to%20have%20one%20format%20or%20form%20that%20automatically%20pulls%20the%20data%20from%20DataSheet5%20if%20%22X%22%20value%20is%205%2C%20and%20from%20DataSheet11%20if%20%22X%22%20value%20is%2011.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20this%20be%20done%3F%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-282020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282220%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282220%22%20slang%3D%22en-US%22%3E%3CP%3Eyou%20cna%20use%20INDIRECT%20to%20create%20a%20changing%20reference%20for%20example%20if%20your%20sheets%20are%20all%20starting%20with%20the%20same%20name%20and%20a%20changing%20number%20following%20it%20and%20put%20that%20number%20in%20another%20cell%2C%20lets%20say%20A1%2C%20you%20can%20create%20a%20formula%20like%20below%20to%20referecen%20B1%20cell%20of%20different%20worksheets%20by%20changing%20the%20value%20in%20A1%20cell%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDIRECT(%22DataSheet%22%20%26amp%3BA1%20%26amp%3B%22!%24B%241%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20the%20worksheet%20do%20not%20exists%20the%20formula%20will%20return%20%23REF!%20error%3C%2FP%3E%3C%2FLINGO-BODY%3E
Joe Trow
Occasional Visitor

Currently using excel 2013 - my question (or what I want to do) is this:

 

I want to have a sheet (format that is printed out) but the information on that sheet is taken from a different tab - based on a value. For example:

I have a workbook with Tabs Labeled DataSheet1 to DataSheet20 - I want to be able to have one format or form that automatically pulls the data from DataSheet5 if "X" value is 5, and from DataSheet11 if "X" value is 11.

 

How can this be done?

Thanks for your help.

1 Reply

you cna use INDIRECT to create a changing reference for example if your sheets are all starting with the same name and a changing number following it and put that number in another cell, lets say A1, you can create a formula like below to referecen B1 cell of different worksheets by changing the value in A1 cell:

 

=INDIRECT("DataSheet" &A1 &"!$B$1")

 

if the worksheet do not exists the formula will return #REF! error