correct function/formula to return a row for each date of a vehicle hire - VBA? VLOOKUP? MATCH?


New Member
Jan 31, 2019

sorry new to forum and pretty new to VLOOKUP, VBA etc, have played with various things that half do what I want but can't find exact solution

I have two workbooks, one where vehicle hire booking's are stored there is a start date column, end date column and registration column, I then have a separate workbook where I want it to look at first workbook and create a row for each date between the start and end date and copy the registration over so I can enter drivers details and other stuff like have they done a drivers check that day, problem also being that in a season we may have same vehicle registration on hire multiple times the only unique identifier in these cases would be the festival name column.

pls help, I had the registration being returned in second workbook by using ='[2019 critical insurance.xlsx]HGV'!$B20

and start date being returned on one row using =VLOOKUP([@Registration],'[2019 critical insurance.xlsx]HGV'!$B$2:$K$35,6)

but then I am still having to manually do the rows for the other dates, this also then mucked up the registration lookup as I had overwritten the cells that contained $B2 through to $B19 so they were no longer returned, then as soon as I have the same registration twice it removed the first hire completely from the second work book

I then played with using a VBA code to run the dates but end up with similar problems, I also have problems when I try to use VLOOKUP to return the festival name due to registration appearing more than once it ignores the GREE entry and only returns the BOOM entry when using =VLOOKUP([@Registration],'[2019 critical insurance.xlsx]HGV'!$B$2:$M$35,11)

example of first workbook below

IDRegistrationHire StartHire EndFestival
1HN16 VNK2/4/195/4/19GREE
2HN16 VNK15/8/1918/8/19BOOM
3R916 YMS02/5/196/5/19GEN


would it help to ID each row entry and in the first workbook and use that somehow, do I need to use INDEX/MATCH with VLOOKUP somehow

example of what I want second workbook to show below, I'm using excel 2016 on windows 10

IDDateRegistrationFestivalDriver Name
12/4/19HN16 VNKGREE
23/4/19HN16 VNK
34/4/19HN16 VNKGREE
45/4/19HN16 VNK
515/8/19HN16 VNKBOOM
616/8/19HN16 VNKBOOM
717/8/19HN16 VNKBOOM
818/8/19HN16 VNKBOOM
92/5/19R916 YMSGEN
103/5/19R916 YMSGEN
114/5/19R916 YMSGEN
125/5/19R916 YMSGEN
136/5/19R916 YMSGEN


Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Can you provide in your example the result(s) of the formula you seek to have?
Upvote 0
Never mind; Looking further into your need I no longer have the question/request earlier submitted.
Upvote 0
sorry I'm confused by your reply, the second workbook example I have put in my post is what I wish to see so the column with date needs to populate based int he hire start/end dates in workbook 1

I think I'm getting closer but still struggling abit, I have now given each record in the first workbook an ID and have added a column in 2nd workbook to record this and populate from first workbook using =VLOOKUP('[2019 critical insurance.xlsx]HGV'!$A2,'[2019 critical insurance.xlsx]HGV'!$A:$A,1,0)

then all the other columns where I want the data like registration, festival name, vehicle type etc I have used a version of this formula =INDEX('[2019 critical insurance.xlsx]HGV'!$A$1:$L$28,MATCH($B2,'[2019 critical insurance.xlsx]HGV'!$A:$A,0),12)

this has now solved my problem with the duplicate reg/festival names being ignored but I still only have one row per date with the start date, am I best using VBA code to populate the other dates in rows below all the other cells that are automatically populated and then I can custom sort by date?

now I have put an id column on second worksheet I only have to fill this in for alot of the other fields to self populate now
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back