Forum Discussion
Help with Linking Columns
I have another question for all you geniuses. As you can see i have a master chart here and im trying to link a whole column to another worksheet based on the selection of what "Engine" they select. So at the bottom of this worksheet i have 3 tabs labeled "Engine 12", "Engine 13" and "Engine 14". This sheet you see is the master sheet. So when Joe Williams selects Engine 12 as the engine he will be working on i want the whole column of his info to link to the Engine 12 worksheet automatically. I hope this makes sense to you all and please let me know if you need any other info. Not sure if i need to use a formula or if there is another way to do this. thank you so much everyone
21 Replies
I am more comfortable working with Named Ranges than either Sheets or Cell references so I would tend to reference engine data using a defined Name 'Engine' that refers to
= CHOOSE( @EngineNum, EngineA, EngineB, EngineC)
From there, one has
=INDEX(Engine, rowNum, colNum)
etc.
- I attached the file to hopefully make more sense for you. But what I'm looking for is when Joe enters his info on the master sheet and when he enters the Engine number it automatically links to the corresponding engine worksheet at the bottom. Thank you sir
- I haven’t looked yet into your sample file. Please ensure that it is representative of your actual file. You still have time to edit it while I haven’t yet looked into it. I am in the academe so I never tolerate mistakes in the given data!
- In effect, you suggest using INDEX-CHOOSE. I love CHOOSE but it becomes unnecessary with INDEX using the Reference form.
True. I suggested CHOOSE on the assumption that I was reading data from ranges on the different Engine sheets and aggregating them on the master. It now appears more likely that the task is to transfer a heading from the master sheet to the individual sheets.
This uses INDEX/MATCH
= INDEX( Master, MATCH( Engine, Master[Engine:], 0 ), 0 )
where 'Engine' is a sheet-local name that contains 12,13,14 as appropriate and
Master[Engine] is a structured references to the table on the master sheet.
For the record I have also included an alternative form using FILTER that, at present, only works on Office 365 insider.
= FILTER( Master, Master[Engine:]=Engine )
- What do you mean by "I want the whole column of his info to link to the Engine 12 worksheet automatically"? Do you mean that the information in Columns D to F shall be retrieved from the Sheet corresponding to the Engine selected in Column C? Please attach your sample file.
- Hi sir. Hope this helps. I'm just looking for when Joe William's enters engine "12" in the master sheet all his info in his column automatically transfers to the Engine 12 worksheet on the bottom. Thank you sir and sorry about the confusion just realized what you were asking for. Thank you sir
- I will look into your attached file, tomorrow. I hope to find a solution for you by then.
