Three-entry table
Solved
leemoke
Posted messages
5
Status
Membre
-
leemoke Posted messages 5 Status Membre -
leemoke Posted messages 5 Status Membre -
Hello,
I am proficient with formulas but not with matrices, macros, etc.
I rely on a summary table, school pick-up schedule, filled automatically with formulas based on the individual sheets of the children.
Column A: names of the children
Row 1, cells B to U: school pick-up locations
Column V: Drivers
The boxes are checked with the letter x.
So-and-so (column A, row 4) is picked up in Paris (x marked in row 4, column S), by such a driver (column V, row 4)
I have already completed my 3rd table, the weekly recap by location.
But I'm stuck on the 4th, a weekly recap by driver.
I wanted to create a table by driver, saying, if it’s the schedule of such a driver, then look for their name in the driver column, find the x, and note the location.
Saying it like that makes it simple, right???
Thank you for your help. I am on a fixed-term contract and succeeding with this table would impress my director!!! And knowing that the person I'm replacing wants to leave... it would be good if he remembers me... Thank you for your help.
I am proficient with formulas but not with matrices, macros, etc.
I rely on a summary table, school pick-up schedule, filled automatically with formulas based on the individual sheets of the children.
Column A: names of the children
Row 1, cells B to U: school pick-up locations
Column V: Drivers
The boxes are checked with the letter x.
So-and-so (column A, row 4) is picked up in Paris (x marked in row 4, column S), by such a driver (column V, row 4)
I have already completed my 3rd table, the weekly recap by location.
But I'm stuck on the 4th, a weekly recap by driver.
I wanted to create a table by driver, saying, if it’s the schedule of such a driver, then look for their name in the driver column, find the x, and note the location.
Saying it like that makes it simple, right???
Thank you for your help. I am on a fixed-term contract and succeeding with this table would impress my director!!! And knowing that the person I'm replacing wants to leave... it would be good if he remembers me... Thank you for your help.
3 réponses
Good evening leemoke
An example using a simple table (if I understood correctly what you were looking to achieve)
https://www.cjoint.com/?0EwttLbSgyC
Since you are skilled with formulas, you won't have any trouble adapting them to your table and even combining them into one
Best regards
--
"Imagination is more important than knowledge." A. Einstein
An example using a simple table (if I understood correctly what you were looking to achieve)
https://www.cjoint.com/?0EwttLbSgyC
Since you are skilled with formulas, you won't have any trouble adapting them to your table and even combining them into one
Best regards
--
"Imagination is more important than knowledge." A. Einstein
As for me, my most complicated formula is VLOOKUP... So, now I feel a bit ashamed... Can you break it down?
I suspected while doing research online that the solution was in an INDEX, but wow... I don't understand column F and why "small value"???
Thank you
in the formula in F, n is determined by the current row from F4 to F10, so in F4 n equals 1 and SMALL returns the 1st value in the array made up of the rows from D3:D10, which is 1 since the 1st A is found in the 1st row, in F5 n equals 2 and the formula returns 3 because the 2nd A is in the 3rd position in the range, etc. (the formula only considers A thanks to the IF function)
it's an array formula, so it's probably not easy to adapt with data in 2 workbooks, I've never tried!
Once the rank in the range D3:D10 is determined (thus the row where we're looking for an x), we must also find the position of x in the horizontal range which is done with MATCH, the search range going from An to Cn, with n equal to that returned by the array formula, increased by 2 since the table starts in row 3, so the address of the range is rebuilt with the INDIRECT function which would give for the 1st value (1 returned by SMALL +2 =3) the range A3:C3; x being in the 1st position of this range, MATCH then returns 1.
Finally, with INDEX we look for the value in the array of locations A2:C2 at row 1 and column 1 (value returned by MATCH)
Best regards.