May 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
May 03 2019 01:15 AM
May 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)
etc.
May 03 2019 10:16 AM
May 03 2019 10:29 AM
May 03 2019 10:36 AM
May 03 2019 11:34 AM
May 03 2019 12:15 PM
May 03 2019 01:35 PM - edited May 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 )
May 03 2019 02:58 PM
May 03 2019 07:04 PM - edited May 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
May 04 2019 12:30 AM
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.
May 05 2019 08:25 PM
May 05 2019 09:27 PM
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
May 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
May 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:
=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.
May 06 2019 06:55 AM
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
May 06 2019 07:18 AM
May 06 2019 08:57 AM
May 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?
=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