hi
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
<tbody>
</tbody>
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
<tbody>
</tbody>
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
ID | Registration | Hire Start | Hire End | Festival |
1 | HN16 VNK | 2/4/19 | 5/4/19 | GREE |
2 | HN16 VNK | 15/8/19 | 18/8/19 | BOOM |
3 | R916 YMS | 02/5/19 | 6/5/19 | GEN |
4 |
<tbody>
</tbody>
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
ID | Date | Registration | Festival | Driver Name |
1 | 2/4/19 | HN16 VNK | GREE | |
2 | 3/4/19 | HN16 VNK | ||
3 | 4/4/19 | HN16 VNK | GREE | |
4 | 5/4/19 | HN16 VNK | ||
5 | 15/8/19 | HN16 VNK | BOOM | |
6 | 16/8/19 | HN16 VNK | BOOM | |
7 | 17/8/19 | HN16 VNK | BOOM | |
8 | 18/8/19 | HN16 VNK | BOOM | |
9 | 2/5/19 | R916 YMS | GEN | |
10 | 3/5/19 | R916 YMS | GEN | |
11 | 4/5/19 | R916 YMS | GEN | |
12 | 5/5/19 | R916 YMS | GEN | |
13 | 6/5/19 | R916 YMS | GEN | |
14 |
<tbody>
</tbody>