#n/a

Can someone please help,

Is there any way to not show the error "#N/A" when a formula is waiting for criteria

my formula is ;

=INDEX(Supplier!$C$5:$C$505,MATCH(B12,Supplier!$A$5:$A$505,0),0)

and works fine when i enter a line, but until i enter the line it shows #N/A and looks un professional

Any ideas

Kind Regards

Try

=IF(ISNA(INDEX(Supplier!$C$5:$C$505,MATCH(B12,Supplier!$A$5:$A$505,0),0)),"",INDEX(Supplier!$C$5:$C$505,MATCH(B12,Supplier!$A$5:$A$505,0),0))

VBA Noob

Hi

I never tested out. However, if you use the help from excel on Error.type you will find a workaround.

ERROR.TYPE

Returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists. You can use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error val

Thanks guys, got it working using the ISNA command

Got another problem for you if you would be so kind (i will catch you out)

i have a spreadsheet for entering parts ordered

simple list of parts, but would like a column to show the date ordered without entering it

i.e. when i open sheet and enter a line i would like the date to automatically appear in say column F (to save time)

but

it needs to save it as that date so next time i go into the sheet it still shows that day and not the new day until i enter a new line on that day

i was thinking along the lines of =now() but this only keeps the day that the shet is opened

hopefully i made this clear

any help would be appreciated

kind regards

Hi

Here is a different flavor, but same result

Private Sub Worksheet_Change(ByVal Target As Range)
Dim qRow As Long
Application.EnableEvents = False
qRow = GetRow1()
Worksheets("Sheet1").Cells(qRow, 6) = Format(Now(), "dd-mm-yyyy")
Application.EnableEvents = True
End Sub

Private Function GetRow1()
GetRow1 = Worksheets("Sheet1").Range("A65536").End(xlUp).Row '+ 1
'MsgBox GetRow1
End Function