VLookup #VALUE! & Search For Data.

[fintail99]

Hi there,

In relation to staff timesheet data, I have a spreadsheet within which I have Sheet1 & Sheet2. In Sheet1, I manually enter employee data such as Employee ID, First Name, Surname, Contracted Hours, for it to be picked-up in Sheet2.

In Sheet2, I enter an Employee ID (listed in Sheet1) for a VLOOKUP routine to then pick-up the First Name, Surname & Contracted Hours from Sheet1.

Example:
For First Name: =IFERROR(VLOOKUP($F7,'Staff List'!$F:$I,2,0),"")
For Surname: =IFERROR(VLOOKUP($F7,'Staff List'!$F:$I,3,0),"")
For Contracted Hours: =IFERROR(VLOOKUP($F7,'Staff List'!$F:$I,4,0),"")

In Sheet2, I have a formula which multiplies the Contract Hours by 4.

Example:
=SUM (S7- (I7*4))

Problem 1
In Sheet2, if I do not enter an Employee ID, the Contracted Hours cell remains blank (this is as expected). However, when this is the case, the cell with the above SUM formula shows “#VALUE!”. Would it be possible for this cell to remain blank or display a zero until the Contracted Hours cell is populated?

Problem 2
In Sheet2, once I enter an Employee ID, the First Name & Surname cells are populated (this is as expected). However, I am unable to use Ctrl+F to search for a particular first name or surname as although the cells display a person’s name, the cell comprises a VLOOKUP routine.

Any guidance would be appreciated.

Kind regards,
Ketan

 

[Joe4]

Problem 1:
Check to see if your cell has a value first. Also note that your use of SUM is unnecessary. You aren't summing a range, your a doing some multiplication and subtraction.
So, assuming that column I is the column causing issues, the formula would look something like:

=IF(I7="","",S7-(I7*4))

Problem 2:
In Find/Search, click on "Options", and change the "Look in" option from "Formulas" to "Values".

 

[fintail99]

Thank you very much MrExcel MVP, both problems solved!

 

[Joe4]

You are welcome!