Hi, i have a pivot table (on a worksheet called "pivot") with dates as the row range. eg 1May2013 11:00, 1May 2013 12:00 and so on.
However, the hours from the source data have gaps eg after 1May2013 12:00 you have 1May2013 14:00.
So what im attempting to do is fill these gaps for a 12 hour period. I have done this by opening a new worksheet and setting up
cell B2 of the worksheet to be equal to cell B3 of the pivot table (it will not move from its current location, though data on it will change).
i have then, on the new worksheet, typed in cell B3, " =B2+Time(1,0,0) and copied this across for the next cells to make up the 12 hour period.
Two rows down, i have set up a vlookup for the time value on row B ( eg =vlookup(B2,Pivot!$B$3:$E$50,3,FALSE) ). My problem is i get #N/A errors,
not only on the cells where i have manually added time, but most of the others. i was expecting errors because i have used the false keyword but only
under the new manually calculated time fields.
I used =IFERROR(VLOOKUP(C1,PIVOT!$B$3:$E$50,3,FALSE),"XX") so that all cells with errors are filled with "XX". i have even tried using INDEX/MATCH with the same result.
Is there something im missing please? this is very frustrating and your help is appreciated.
Bookmarks