Help with Linking Columns

Brass Contributor

Capture.JPG

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
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.

@spalmer 

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.

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.
In effect, you suggest using INDEX-CHOOSE. I love CHOOSE but it becomes unnecessary with INDEX using the Reference form.
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.
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

@Twifoo 

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.

@spalmer 

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 )

 

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 

@spalmer 

In my opinion there is no need for a dozen separate sheets and hundreds of formulas.

Just use the filter on the master sheet and you're done.

 

 

Given your data in the Master sheet, please fill in Engine 12 sheet so I can see the results you want therein.

ok i did it.  So if Mike were typing all his info in, all that would auto populate in the Engine 12 Worksheet.  Thank you Sir

@Twifoo 

i appreciate your thoughts, but the only thing about that is i dont want these employees doing any of that cause some are not the best at excel and dont want them to mess anything up haha.  so if it auto-populates then it just makes it easier for everyone to read by just clicking the worksheet. @Detlef Lewin 

@spalmer 

In the attached file, I modified the Column Labels in G4, H4, and K4 in the Master Sheet to CPR Expiry, Evaluation, and Card Expiry. The corresponding Column Labels in Engine 12 Sheet were also modified to conform thereto. The purpose of the modification is to make those Column Labels unique. You can further modify such Column Labels, provided you ensure they are unique. 

In the Master Sheet, I added a Helper Column for Count with this formula: 

=COUNTIF(L$5:L5,L5)

In the Engine 12 Sheet, I also added a Helper Column for Count with this formula: 

=ROW()-2

Note that in the Engine 12 Sheet, I inserted a top row, wherein A1 returns the Sheet Name with this formula: 

=RIGHT(CELL("filename",A1),
LEN(CELL("filename",A1))-
FIND("]",CELL("filename",A1)))

The formula in A3, copied down rows and across columns, in the Engine 12 Sheet is: 

=IFNA(INDEX(Master!$B$5:$M$92,
MATCH($A$1&"|"&$K3,INDEX(Master!$L$5:$L$92&"|"&Master!$M$5:$M$92,0),0),
MATCH(A$2,Master!$B$4:$M$4,0)),"")

If the foregoing formula fulfills your requirements, you can do the same in the other Engine Sheets. 

you are the Man! Thank You so much Sir.  you have no idea how much i appreciate your time and effort!  i am heading out of town for the week so when i get back ill check it out but it looks like it will work.  Again, Thank You Sir @Twifoo 

Good luck! I look forward hearing from you soon.

@spalmer 

I wonder what can get messed up if the filter is used?

 

Twifoo it works perfectly!  thank you so much, you are a Genius!  i do have one question just out of curiosity. In your formula below what letter or symbol did you use between the " " that i made bold below?

 

=IFNA(INDEX(Master!$B$5:$M$92,MATCH($A$1&"|"&$K3,INDEX(Master!$L$5:$L$92&"|"&Master!$M$5:$M$92,0),0@Twifoo