listbox problem (e2000)

Topic

New Reply

Hi all

I have a workbook with 2 sheets, sheet 1 = master and sheet 2 = secure

on sheet 2 I have 2 listboxes that calculate a transport rate. Listbox 1 takes its input range from the master sheet as follows master:A6:A405 which then populates the listbox with town names. The second listbox contains details of vehicle sizes so that when the user selects a town from listbox 1 and a vehicle size from listbox 2 a price is returned into the appropraite cell.

Sometimes a customer will request a 2 man crew, for the second man we calculate that a van will travel at an average speed of 50 miles per hour and that he will be paid

Reply | Quote

Replies

The formula

=VLOOKUP(H1,Master!A6:B405,2,FALSE)

looks for the value of H1 in the first column of Master!A6:B405 and returns the value from the second column in the row where the value was found.

Reply | Quote

Thank you Hans

I have entered this information into a cell and it returns a N/A retsult , any ideas please, also can you please let me know what the ,2, part refers to in your response.

Thanks

Stephen

Reply | Quote

Sorry Hans I have given you incorrect information to work on.

What I did not realise is that cell H1 in worksheet 2 shows the row number (from the master sheet) of the destination selected in the listbox, but I cannot see how it is getting its values as there is no formula showing. The list box as mentioned shows its source as Master!A6:A405 and is linked to H1, any other ideas please?

Stephen

Reply | Quote

I am not sure I understand your setup exactly, but does :

=Index(Master!B6:B405,H1)

gIve you what you want?

Steve

Reply | Quote

Thank you Steve

That worked great and returns the mileage for the city selected, in my original question I eluded to the fact that what I was trying to do was to calculate the cost of a 2nd man based on mileage. The result I now get if I slect for example York is 422 which is the mileage from my office to York UK and I can now cost for the 2nd man by formatting a cell to read =G22/50*10 to arrive at the cost, however the minimum charge for a second man is

Reply | Quote

=Max(G22/50*10 ,20)

Steve

Reply | Quote