If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#N/A
Hello from Steved
The below formula gives me #N/A if there is no value in the cell. Could you please tell how I can make the cell blank, until a value is added. =INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)) Thankyou. |
#2
|
|||
|
|||
#N/A
Hi Steved!
=IF(ISNA(INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,))) IOW you use a standard construct: =IF(ISNA(YourFormula),"",YourFormula) -- Regards Norman Harker MVP (Excel) Sydney, Australia |
#3
|
|||
|
|||
#N/A
You had a few redundant commas in there, so see how this works:
=IF(ISNA(MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000), MATCH("Surname",'Audit Team'!$A$1:$C$1))) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Steved" wrote in message ... Hello from Steved The below formula gives me #N/A if there is no value in the cell. Could you please tell how I can make the cell blank, until a value is added. =INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)) Thankyou. |
#4
|
|||
|
|||
#N/A
Hello Norman from Steved
I'm getting an error. It is highlighting "" Can you assist me as to what my problem is please. Thankyou. -----Original Message----- Hi Steved! =IF(ISNA(INDEX('Audit Team'!$A$1:$C$2000, MATCH (E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH (E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,))) IOW you use a standard construct: =IF(ISNA(YourFormula),"",YourFormula) -- Regards Norman Harker MVP (Excel) Sydney, Australia . |
#5
|
|||
|
|||
#N/A
Hi Steved!
Try deleting the "" and re-entering them. I find that if I copy and paste formulas there are occasions when the "" are interpreted wrong / differently. -- Regards Norman Harker MVP (Excel) Sydney, Australia "Steved" wrote in message ... Hello Norman from Steved I'm getting an error. It is highlighting "" Can you assist me as to what my problem is please. Thankyou. -----Original Message----- Hi Steved! =IF(ISNA(INDEX('Audit Team'!$A$1:$C$2000, MATCH (E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH (E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,))) IOW you use a standard construct: =IF(ISNA(YourFormula),"",YourFormula) -- Regards Norman Harker MVP (Excel) Sydney, Australia . |
#6
|
|||
|
|||
#N/A
Thankyou Norman
-----Original Message----- Hi Steved! Try deleting the "" and re-entering them. I find that if I copy and paste formulas there are occasions when the "" are interpreted wrong / differently. -- Regards Norman Harker MVP (Excel) Sydney, Australia "Steved" wrote in message ... Hello Norman from Steved I'm getting an error. It is highlighting "" Can you assist me as to what my problem is please. Thankyou. -----Original Message----- Hi Steved! =IF(ISNA(INDEX('Audit Team'!$A$1:$C$2000, MATCH (E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH (E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,))) IOW you use a standard construct: =IF(ISNA(YourFormula),"",YourFormula) -- Regards Norman Harker MVP (Excel) Sydney, Australia . . |
#7
|
|||
|
|||
#N/A
Thankyou RD
-----Original Message----- You had a few redundant commas in there, so see how this works: =IF(ISNA(MATCH(E2:E2001,'Audit Team'! $A$1:$A$2000)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'! $A$1:$A$2000), MATCH("Surname",'Audit Team'!$A$1:$C$1))) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Steved" wrote in message ... Hello from Steved The below formula gives me #N/A if there is no value in the cell. Could you please tell how I can make the cell blank, until a value is added. =INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'! $A$1:$C$1,)) Thankyou. . |
Thread Tools | |
Display Modes | |
|
|