Hello,
first of all sorry for my bad englisch. I`m dutch. I`m creating a timetable for my colleague at work. On the left side I planned all the workers on shifts. Now I want to the shifts automaticly filled in on the right side vertically. Offcourse wenn I change a number on the left side it has to change on the right side. Eventually this timtable will be for over 100 workers.
If I`m not clear please let me know.
Greetz Patrick
I am not sure if this is what you need. If not, perhaps explain more on the form with arrows. etc.
Yes,
this is exactly how it should look like but now it@s not automatic. For example, if I change the first shift for worker 1 from 0600-1500 to 1200-2100 this change also has to be made on the right side. Lik I said in the end this timetable will be for 100 workers so I don@t want to copy all the cells one by one every 4 weeks.
Copy this into I2:
=IFERROR(INDEX($B$2:$F$23,MATCH(I$1,OFFSET($A$2:$A$23,0,MATCH($H2,$B$1:$F$1,0)),0)-1,MATCH($H2,$B$1:$F$1,0)),"")
And paste over and down as far as needed.
pbeumer.xlsx
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Nice,
this I exact what I need. Now I have to transfer this to my big timetable. Thank you very much.
I succesfully used the formula on my major timetable. Now I want to learn what this formula did but I don`t know really how to read it. Can you tell me in several steps what your formula does?
The formula is called index and match. You can google "excel index and match" and see how it works.
There's a few formulas at work here.
The first is IFERROR which defaults to an output should the first operation result in an error. In this case, we are defaulting to "" which means blank.
I also used INDEX and MATCH.
Parameters:
MATCH(look for what, look for it where, 0 for exact match)
INDEX(in what table, pull value from the nth row, pull value from the nth column)
Since you have multiple sets of data for each header, we also used OFFSET.
OFFSET(what reference, how many rows, how many columns)
So, OFFSET(A1,1,1) really pulls the value of B2 which is one row down and one column over.
Putting it altogether, I am indexing your whole table, and using offset to choose a variable column. When it finds an exact match for the row, I'm pulling the value from directly above.
Well thank you but this is going far above my head. I`m dutch so wenn I have the time this weekend I look all the stuff up in Dutch and see if I can read it. Wenn I have a new problem I definitely go to this forum!!!!
Thanx Thanx Thanx!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks