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

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies