Using ISNA function, but replace the blank cell with text
 

I have a project that requires a cell to change with different variables.
The cell can either be blank, use a number from a vlookup that is lower than
listed in the vlookup at which time the text message "customer n. than...
appears, or the number is in the vlookup range, or the number is greater than
the numbers in the vlookup and enters an "NA" result at which I used the ISNA
function which then in turn leaves the cell blank. The cell needs to have
the text "customer n. invalid" in the cell. The following is the formula I
have so far and the only thing it does not do is enter the text "customer n.
invalid" when the result is false.

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than
"&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,customers!A3:A11,1,FALSE)), "Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))))

I would appreciate any help with this. I have spent many hours trying to
make this work. My whole project is full of these type of excel formulas.
--
Minnie

Using ISNA function, but replace the blank cell with text
 

Try this:

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater
than
"&customers!$A$3,IF(ISNA(VLOOKUP(E8,cu*stomers !
A3:A11,1,FALSE)),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))

Hope this helps.

Pete

On Jun 22, 1:31*am, Minnie wrote:
I have a project that requires a cell to change with different variables. *
The cell can either be blank, use a number from a vlookup that is lower than
listed in the vlookup at which time the text message "customer n. than....
appears, or the number is in the vlookup range, or the number is greater than
the numbers in the vlookup and enters an "NA" result at which I used the ISNA
function which then in turn leaves the cell blank. *The cell needs to have
the text "customer n. invalid" in the cell. *The following is the formula I
have so far and the only thing it does not do is enter the text "customer n.
invalid" when the result is false.

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than
"&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,cu*stomers!A3:A11,1,FALSE)) ,"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))))

I would appreciate any help with this. *I have spent many hours trying to
make this work. *My whole project is full of these type of excel formulas.
--
Minnie

Using ISNA function, but replace the blank cell with text
 

That's great Pete! It worked for replacing the "NA" result with "customer n.
invalid" but now when I enter a valid number, the valid number also shows up
in the cell that should be blank.
--
Minnie


"Pete_UK" wrote:

Try this:

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater
than
"&customers!$A$3,IF(ISNA(VLOOKUP(E8,cuÂ*stomer s!
A3:A11,1,FALSE)),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))

Hope this helps.

Pete

On Jun 22, 1:31 am, Minnie wrote:
I have a project that requires a cell to change with different variables.
The cell can either be blank, use a number from a vlookup that is lower than
listed in the vlookup at which time the text message "customer n. than....
appears, or the number is in the vlookup range, or the number is greater than
the numbers in the vlookup and enters an "NA" result at which I used the ISNA
function which then in turn leaves the cell blank. The cell needs to have
the text "customer n. invalid" in the cell. The following is the formula I
have so far and the only thing it does not do is enter the text "customer n.
invalid" when the result is false.

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than
"&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,cuÂ*stomers!A3:A11,1,FALSE) ),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))))

I would appreciate any help with this. I have spent many hours trying to
make this work. My whole project is full of these type of excel formulas.
--
Minnie

Using ISNA function, but replace the blank cell with text
 

I think you want to do this, then:

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater
than "&customers!$A$3,IF(ISNA(VLOOKUP(E8,customers!
A3:A11,1,FALSE)),"Customer number is invalid","")))

Hope this helps.

Pete

On Jun 22, 2:19*am, Minnie wrote:
That's great Pete! It worked for replacing the "NA" result with "customer n.
invalid" but now when I enter a valid number, the valid number also shows up
in the cell that should be blank.
--
Minnie



"Pete_UK" wrote:
Try this:

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater
than
"&customers!$A$3,IF(ISNA(VLOOKUP(E8,cu*stomers !
A3:A11,1,FALSE)),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))

Hope this helps.

Pete

On Jun 22, 1:31 am, Minnie wrote:
I have a project that requires a cell to change with different variables. *
The cell can either be blank, use a number from a vlookup that is lower than
listed in the vlookup at which time the text message "customer n. than....
appears, or the number is in the vlookup range, or the number is greater than
the numbers in the vlookup and enters an "NA" result at which I used the ISNA
function which then in turn leaves the cell blank. *The cell needs to have
the text "customer n. invalid" in the cell. *The following is the formula I
have so far and the only thing it does not do is enter the text "customer n.
invalid" when the result is false.

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than
"&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,cu**stomers!A3:A11,1,FALSE) ),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))))

I would appreciate any help with this. *I have spent many hours trying to
make this work. *My whole project is full of these type of excel formulas.
--
Minnie- Hide quoted text -

- Show quoted text -