Vlookup dilemna

[Beckwa]

Ok, for my spreadsheet for a taxi company I have a cell for "base charge" and one for "price for mile". I want these prices to be dependent on a Vlookup which refers to a lookup prices table. The prices change according to the current time. I tried a Vlookup but that doesn't include the fact that it should be referring to the time before looking up the appropriate value. Any ideas? Am I even speaking English anymore?!?!? B

 

[Yogi Anand]

Hi Becky:
Just as a reminder, make sure that the entry H7 in the worksheet 'Booking Form' has the range name BookingTime.

 

[Beckwa]

Yeah already done that I sent u an email which might help further

 

[Yogi Anand]

On 2002-04-27 15:03, Beckwa wrote:
Yeah already done that I sent u an email which might help further

Well Becky, I changed your 'Price Table' layout to:

Time Cost Per Mile Base Charge
4.01 £1.05 £2.00
10:01 £0.75 £1.15
16:01 £0.85 £1.40
22:01 £0.95 £1.80
and adjusted the formulas in your 'Booking Form' accordingly. I hope it works for you now.

Regards!

Yogi Anand

 

[Beckwa]

Nope still isn't changing with the time changes. It stays the same. Thanks for all ur help but i'm gonna leave u alone cos u've got better things to do, B

 

[Yogi Anand]

Hi Becky:
I have adjusted a few things and the 'Base Charge' and 'Cost Per Mile' compute correctly for all time segments. Now don't mess with cell H7 -- if you want to test for different time segments, change your system time. The cell H7 is no longer =NOW() but it is =NOW()-TODAY() to access the Time element only -- you can't just put time like 16:00 in cell H7 of your booking form. I have locked and hidden that cell so the user can not mess with that cell.
Please do post back if it works for you ... otherwise explain a little further and let us take it from there!
NB: If you must mess around with making direct entries in cell H7, then instead of using a VLOOKUP table we will have to use nested IF functions.

 

[Beckwa]

Well it all works fine - thank you soooooooooo much for ur help!!! ONe question you know the H7 cell now says "NOW()-TODAY(" - what does this mean/do??? Thanks again, B

 

[Yogi Anand]

On 2002-04-28 01:35, Beckwa wrote:
Well it all works fine - thank you soooooooooo much for ur help!!! ONe question you know the H7 cell now says "NOW()-TODAY(" - what does this mean/do??? Thanks again, B

Hi Becky:
We have based the Prices Table on time of the day regardless of what day it is. Having stated that =NOW() function combines the numeric expression for the date and time combined, and =TODAY() gives the numeric expression for the date only. So to get only the numeric expression for time and not mix it up with the date, I had to use the =NOW()-TODAY() function to make things work to your satisfaction.

Hope This Helps!