User Input with Vlookup

[johnfar]

Hi there,

I have produced a spreadsheet so users have a record of the jobs they do in a particular month.
To simplify.
Lets say I have a table of 3 columns. In columnA I have the job number i.e. 1,2,3 and in Columns B and C I have the start and finish times for that job, respectively. These times are fixed and I have named this range of cells to be used with the lookup function.

Now on a seperate worksheet I have a colA of dates for the current month and in colB the user is invited to enter the corresponding job they did on that particular day. The user enters the job number by selecting the job from a data validation list. Depending on what job they select, Col
C shows the start time and ColD the Finish time, by using the vlookup function to return the corresponding colums from the initial table created.

Now here's my problem. In this example we have 3 jobs with fixed start/finish times in a lookup table. Now lets say I introduce a fourth job. This has no fixed start/finsih time but is also included in the lookup table. What code can I use in the cells for the start and finish times for job 4 so that when 4 is selected from the data validation list the user is prompted to enter a start and finish time which then appears in ColC and D in our second sheet.

Many thanks in advance

[Dionysos]

Have you thought about using a data form to add, edit, find, and delete rows in your data?

http://office.microsoft.com/en-gb/ex...010236698.aspx

You can also create a macro to auto open the data form when the spreadsheet is opened.

[johnfar]

Data Form doesn;'t quite seem what I'm after.
I have attached a copy of my spreadsheet.
Iwant to add an additional job to my list of duties say for eample "Spare". The suer then adds the start and finish times whilst preerving the underlying formulas.

[Dionysos]

I've added two new functions to your spreadsheet:

Please Login or Register  to view this content.

I have also modified the "if" statements in columns D and E to leverage the functions if the value in column C is not defined or not in the lookup table. If the value doesn't exist it prompts the user to enter the start and end times. To test, enter an arbitrary value in column C.

[johnfar]

many thanks Dionysos, but i cannot open the spreadsheet. Any ideas?

[Dionysos]

Try it now.

[johnfar]

Try it now.

Keeps saying Excel cannot open file because the file or file extension is not valid. I have excel 2007. Is the file corrupt by any chance. Is there another way I can download the file?

Your help is much appreciated.

[Dionysos]

I tested your original file and it seems to be corrupt as well. May be the attachment manager. If this one doesn't work then I give up.

[johnfar]

iT WORKS!!!
Thanks for your efforts.

[johnfar]

When i enter an arbitrary value in Col C it asks for end time before start time. Can these be switched? i.e. request start time first.

[Dionysos]

Not sure what triggers one function to fire before the other. I tried switching the columns just to see if it was the order of the cells in the spreadsheet, but that didn't have any affect. Anyone else out there know why one function fires before another if they are triggered by the same event and how to change it?