vlookup errors #n/a

[R.gillespie]

I am having problem trying to get rid of #n/a error when i don't enter a
value.
Anyone that can help, i would appreachate it.
here is the cells:
50mb £5.00 £5.01
100mb £10.00 £10.01
250mb £25.00 £25.01
#N/A #N/A
#N/A #N/A
#N/A #N/A
And formulae i am using
=VLOOKUP(J4,'Tariff Info'!$Q$3:$S$17,2,FALSE).


Also if anyone know how to add a button to finish processing the deal, and
print to a templete that would be brill...

Richard
(e-mail address removed)

 

[Sheeloo]

Try
=ISNA(VLOOKUP(J4,'Tariff Info'!$Q$3:$S$17,2,FALSE),"",VLOOKUP(J4,'Tariff
Info'!$Q$3:$S$17,2,FALSE))

 

[R.gillespie]

Hi
It said " you have entered to many arguments for this function" at the ""
point. I could email it to you to see?
Thanks Richard

 

[T. Valko]

Try it like this:

=IF(ISNA(VLOOKUP(J4,'Tariff Info'!$Q$3:$S$17,2,0)),"",VLOOKUP(J4,'Tariff
Info'!$Q$3:$S$17,2,0))


--
Biff
Microsoft Excel MVP


"(e-mail address removed)"

 

[R.gillespie]

Hi
No its making my formulae that was working display #n/a.
Richard

 

[T. Valko]

I don't see how that's possible.

If this works (doesn't return #N/A):

=VLOOKUP(J4,'Tariff Info'!$Q$3:$S$17,2,FALSE)

Then this will also work and will trap any #N/A errors:

=IF(ISNA(VLOOKUP(J4,'Tariff Info'!$Q$3:$S$17,2,0)),"",VLOOKUP(J4,'Tariff
Info'!$Q$3:$S$17,2,0))


--
Biff
Microsoft Excel MVP


"(e-mail address removed)"

 

[Dave Peterson]

Try Biff's formula once more.

If it doesn't work, post the version of the formula you're using.

And check to see that you don't have any N/A errors in column R of Tarrif info.

 

[R.gillespie]

Yes got it working now thanks guys.
Is it possible to have a print button on the excel sheet, so i can print the
tariff details when the sales rep was entered them?

Richard

 

[ShaneDevenshire]

Hi,

1. I know you have this working, but it looks like the 50mb stuff is in
column J based on your formula, so you solution could be a little simpler:

=IF(J4="",VLOOKUP(J4,'Tariff Info'!$Q$3:$S$17,2,FALSE))

2. If you don't want to print errors but you don't mind seeing them in the
spreadsheet you can choose File, Page Setup, Sheet tab and change Cell errors
as to <blank>.

3. If you don't want to handle errors via formulas in the spreadsheet you
can apply conditional formatting to the cells to hide the display of errors.
Choose Format, Cells, Formula is from the first drop down, and enter the
formula
=ISNA(K9)
click the Format button and choose White under the Color drop down on the
Font tab.

 

[Sheeloo]

I did not put an IF around the formula I had provided...
Sorry... :-(

You should use
=IF(ISNA(VLOOKUP(J2,'Tariff Info'!$Q$3:$S$17,2,FALSE)),"",VLOOKUP(J2,'Tariff
Info'!$Q$3:$S$17,2,FALSE))

 

[Dave Peterson]

Why not just hit the print icon on the toolbar or ribbon/QAT?

But you could record a macro when you print your worksheet, then assign this
macro to a button (from the Forms toolbar) placed on the worksheet.