Forum Discussion
Help with Linking Columns
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!
here is the actual spread sheet. thank you again so much Twifoo for your time. So as more people add to the master list it automatically links more people to the designated worksheet that they chose under "Unit" So when i click on Engine 12 worksheet it will show me all the people that are on engine 12 instead of sorting through the Master worksheet. thank you so much Sir. Twifoo
- 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 )
- Thank you sir I would really appreciate it. I will try using both and see if I can figure it out. If I do I'll let ya know. Thank you so much for your time. But if you figure it out please let me know.
