Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel N/A error message


  • Please log in to reply

#1
ellac

ellac

    New Member

  • Member
  • Pip
  • 3 posts
Hi everyone

I am desperate for an answer. Here is an example of what I have:

Column E Column F
FOODSERVICE OTHER
#N/A #N/A
FOODSERVICE Check
#N/A #N/A
#N/A #N/A
MInt OTHER
#N/A #N/A
#N/A #N/A

I have a vlookup that looks up two other spreadsheets and brings in info. I get an #N/A which makes sense, but what I need it to do is for every title change I need the #N/A to be replaced with the title above it.

The problem is that it is a long list and titles change. I thought maybe there is some vba code that can help me.

Thanks everyone
  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
If the output you were expecting was as follows;

Column E Column F
FOODSERVICE OTHER
FOODSERVICE OTHER
FOODSERVICE Check
FOODSERVICE Check
FOODSERVICE Check
MInt OTHER
MInt OTHER
MInt OTHER


Then I have understood your question and VB may not be necesarry.

Here is a sample formula from cell B9
If the lookup value in cell A9 is not found (#N/A) then the ISERROR is true and the value from B8 will be returned instead.

=IF(ISERROR(VLOOKUP(A9,$G$1:$H$16,2,FALSE)),B8,VLOOKUP(A9,$G$1:$H$16,2,FALSE))


Hope this helps

Regards
David
  • 0

#3
ellac

ellac

    New Member

  • Topic Starter
  • Member
  • Pip
  • 3 posts
Hi David

Thanks, it does work to a point but the problem is that in column E and F the titles change every few cells down so it will not always point to a specific cell.

Is there an addition to the vlookup or am I doing something wrong.

Thanks

Ella
  • 0

#4
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
Ella,
My testing worked OK.

Try checking the formula that you copied down and check that the NA is pointing to the correct cells.

Check that you are using the full formula components that I provided.

If you are still having problems, attach a small sample of data with the formula intact and we can reveiw further.


Regards
David
  • 0

#5
ellac

ellac

    New Member

  • Topic Starter
  • Member
  • Pip
  • 3 posts
Hi David

First of all thanks so much for taking the time to help me, I guess I am not very good at formulas and that is where my problem is.

Here is an example of the info, each time a title changes it should pick up the title not the N/A, this particular example starts on cell E6.

E F
6 CLUB COSTCO
7 0 0
8 #N/A #N/A
9 #N/A #N/A
10 #N/A #N/A
11 CLUB SAM'S
12 #N/A #N/A
13 CLUB SAM'S
14 #N/A #N/A
15 #N/A #N/A
16 #N/A #N/A
  • 0

#6
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
Ella,
I am attaching an example spreadsheet.
It shows the results you require vs the the results you typically encounter.

I hope this helps you better understand what is happening.
Both blocks of data are looking for values in highligted block in the centre.

If this doesn't help you, suggest you attach your spreadsheet you will need to zip it first.


Regards
David

Attached Files


  • 0

#7
jasonharrod

jasonharrod

    Member

  • Member
  • PipPip
  • 18 posts
The reason the #N/A show up if the data is in both spreadsheets is because the key value that you are entering isn't the same. If the spreadsheets look the same try doing a trim() function on the key column or select the column and do a replace find " " replace with nothing. this will trim out the white space in the key column.


Hope that helped.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP