Spreadsheet idea containing formulas over my current skill level...

[blaze89]

Hello all,

This is my first post here so hello everyone, pleased to meet you all I'm a self taught Excel user so my skills at this stage are unfortunately a bit limited and quite rough on the edges as well.

I recently started working for a new company and I'm working on a spreadsheet that'll make my and my colleagues lives easier. We've got a sheet with driver names and the delivery locations. Until now, the guys here were retyping all the locations manually from another spreadsheet and matching them to the appropriate driver for the particular day. It looks like this:

The database from the other spreadsheet, after being pasted into an additional sheet in the file from the screen shot up looks like this:

What I want the spreadsheet to do is that you copy the database into a blank sheet in the file and it automatically fills itself in with the correct locations to the appropriate drivers. I was thinking of using the formulas INDEX and MATCH, but I am not sure how to modify the formula in order for it to find the data I want. When I use =INDEX(Sheet1!$4:$4,MATCH('Driver Names'!C4,Sheet1!$12:$12,0)) it sends me directly above the Driver ID in the database sheet and I want it to find the data 2 cells to the right instead. There is also a possibility for a driver to have several locations for 1 run, which means probably the IF formula, that IF the cell 2 cells to the right is full then return the value in the same cell after a coma, for example. I figured that the CONCATENATE formula might come in place in this instance, but I am really clueless how to make it all work together nice and tidily. There are also 2nd runs for drivers, which means the same name is going to appear in the database sheet 2x, so I guess the IF function again for if the 1st RUN cell is filled in with a location already then find another same Diver ID in the sheet and use the 2nd data and not the 1st one. It is a really complicated spreadsheet for me and I need some guidance from you guys. I'm still learning the arts of Excel so please forgive my noobness If anything is not clear or my explanation seems a bit off, please don't hesitate to ask me for clarification.

Thanks in advance for any advice!

 

[Rijnsent]

Hi Blaze,

if possible, please use shorter formulas and more (hidden) helper columns, it will make your life debugging much easier (and if someone after you needs to maintain the spreadsheet, their life too...). If I copy-paste your formula I get an invalid formula, did you copy-paste it completely?

Koen

 

[blaze89]

Hi Koen,

No, the formula which I pasted here is not the whole thing, as it's really long and messy-like, for now I'm just trying to make it work like I want it to work and afterwards I'll take care of the aesthetics. On a side note, I've always used the long-*** formulas and I don't really know how to shorten them, so that requires additional research from my side...

=IF(MATCH(C4, OFFSET(Sheet1!$12:$12, 0, E33, 1, COLUMNS(Sheet1!$12:$12)-E33), 0)+E33

That's formula F33 from your previous post, but mine starts with an IF formula, so the part I pasted is just the logical bit for the IF function. The "if true" value for the IF function is the exact same formula I have in cell E4:

but because it's ridiculously long I'll skip pasting it here. So the formula looks more or less like this (in a short form):

=IF(MATCH(C4, OFFSET(Sheet1!$12:$12, 0, E33, 1, COLUMNS(Sheet1!$12:$12)-E33), 0)+E33, *formula from E4*, "")

What this formula now does is it looks up the 2nd occurrence of the Driver's name in Sheet1 and instead of returning the value of the 2nd occurrence's locations, it returns the values of the 1st occurrence's locations. Do I need to change the formula step 1: =MATCH('Driver Names'!C4,Sheet1!$12:$12,0) step 2: =OFFSET('Driver Names'!$A$4,0,STEP1+1) from cell E4 for it to find the 2nd occurrence's locations?

 

[blaze89]

Or maybe just how do I make excel skip the 1st find [=OFFSET('Driver Names'!$A$4,0,STEP1+1)] and make it jump straight to the 2nd occurrence of the same value? In this particular situation, I want the formula to do exactly the same thing it did for cell C4, but I want it to return the values put at the 2nd occurrence of the Driver's name. I managed to put everything else together, it's just this part that I still find tricky.