I looked at your file. I'm embarassed to say that the dummy data I created
Friday didn't have any duplicate values, and that the solutions offered won't
work if there are duplicates.
However, here's an easy solution that WILL work:
1. On the MILES sheet, insert a new column between C & D (it becomes your
new column D, and the miles to be returned are now column E). In D3, enter:
=B3&C3
Copy this formula down for as many rows as have data on the MILES sheet. We
are creating a single concatenated field against which we can easily do a
Vlookup.
2. On the BLANK sheet, enter the following formula in D18:
=IF(ISERROR(VLOOKUP(B18&C18,Miles!D:E,2,FALSE)),"" ,VLOOKUP(B18&C18,Miles!D:E,2,FALSE))
Copy this formula down through all the rows on your form. This formula
concatenates the From and To sites, does a Vlookup with it on the MILES
sheet, and returns the mileage. If the Vlookup fails (can't find a match),
nothing is displayed.
You also have a few typos:
milage should be mileage
monthely should be monthly
mileometer should be odometer
Hope this helps,
Hutch
"bikky" wrote:
WOahhh
Thanks for all the help you've supplied, i'll run with the match
script, as i don't want fixed reference to Blank! as that is the base
sheet to be copied to each months new worksheet.
The Match isn't quite working yet, as i either get the correct milage
for the first entry in the match lookup, or 0 or NA.
If your wanting a copy of the doc i can post to a website for you's to
have a look, NOTE the miles sheet with the list of sites isn't complete
yet, its a work in progress, so i'll have to easily adjust the formulae
as more "regular" trips are required. The start and end of journey
also needs freetext option for "sporadic" journey's
infact check www.lan-uk.derwentside.net/milage.xls (it is virus free,
my own hosted server)
rather than posting back the spreadsheet, pasting the formula in here
would be more helpful for me to learn, and others to reference from.
THANKS V MUCH for all the help so far.
spencer
--
bikky
------------------------------------------------------------------------
bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149
View this thread: http://www.excelforum.com/showthread...hreadid=559359