Please can someone help me correct a formula, which I nearly have working, but not quite!
Column A in my worksheet contains some cells with people's names, some with dates, and some blanks. I am trying to create a formula which when I drag it down through a range, doesn't return #N/A for the rows containing a blank, or a date.
So, to explain further, sheet 2 contains a further table of data, with names in column A. I use Vlookup to extract the data I need from sheet 2 and pull it into sheet 1, depending on the person's name entered into column A of sheet 1.
One early example which works fine is as follows:-
=IF(E16="O",((VLOOKUP($A16,data,12,FALSE))),0)
This formula can be dragged down and works fine, because cell E16 will only contain a letter, where there is a person's name in column A.
The formula I am trying to correct is failing because one of the logical tests, is based on a value from within the sheet 2 table of data, and such a value can only be obtained if column A of sheet 1 contains a real name. If it contains a blank or a date, that test returns a #N/A value and my formula, which works in all other ways, returns #N/A, whereas I actually want it to return zero.
I will post my formula below, which looks horribly complicated though it does work essentially, but can anyone help me with this one final issue, please?
=IF(AND(VLOOKUP($A16,data,7,FALSE)>0,G16>0),(G16*$G$11*D16),IF(AND(VLOOKUP($A16,data,7,FALSE)=0,G16>0),((VLOOKUP($A16,data,12,FALSE)*G16*$G$11)),0))
Thanks
Neil
Column A in my worksheet contains some cells with people's names, some with dates, and some blanks. I am trying to create a formula which when I drag it down through a range, doesn't return #N/A for the rows containing a blank, or a date.
So, to explain further, sheet 2 contains a further table of data, with names in column A. I use Vlookup to extract the data I need from sheet 2 and pull it into sheet 1, depending on the person's name entered into column A of sheet 1.
One early example which works fine is as follows:-
=IF(E16="O",((VLOOKUP($A16,data,12,FALSE))),0)
This formula can be dragged down and works fine, because cell E16 will only contain a letter, where there is a person's name in column A.
The formula I am trying to correct is failing because one of the logical tests, is based on a value from within the sheet 2 table of data, and such a value can only be obtained if column A of sheet 1 contains a real name. If it contains a blank or a date, that test returns a #N/A value and my formula, which works in all other ways, returns #N/A, whereas I actually want it to return zero.
I will post my formula below, which looks horribly complicated though it does work essentially, but can anyone help me with this one final issue, please?
=IF(AND(VLOOKUP($A16,data,7,FALSE)>0,G16>0),(G16*$G$11*D16),IF(AND(VLOOKUP($A16,data,7,FALSE)=0,G16>0),((VLOOKUP($A16,data,12,FALSE)*G16*$G$11)),0))
Thanks
Neil