05-02-2019 08:46 PM
05-02-2019 08:46 PM
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
05-03-2019 01:15 AM
05-03-2019 02:06 AM
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)
05-03-2019 10:16 AM
05-03-2019 10:36 AM
05-03-2019 11:34 AM
05-03-2019 12:15 PM
05-03-2019 01:35 PM - edited 05-03-2019 01:44 PM
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 )
05-03-2019 02:58 PM
05-03-2019 07:04 PM - edited 05-03-2019 08:18 PM
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
05-05-2019 09:30 PM
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
05-06-2019 01:54 AM
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:
In the Engine 12 Sheet, I also added a Helper Column for Count with this formula:
Note that in the Engine 12 Sheet, I inserted a top row, wherein A1 returns the Sheet Name with this formula:
The formula in A3, copied down rows and across columns, in the Engine 12 Sheet is:
If the foregoing formula fulfills your requirements, you can do the same in the other Engine Sheets.
05-10-2019 10:56 AM
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?
by msm66 on July 13, 2020
by sintx on July 08, 2020