EXCEL FORMULA

T

Taxmom

Help please!

I have a worksheet "receiving data" column1 state; column 2 city county
name; column 3 gross (blank); column 4 tax(blank). I have a "data worksheet"
cities listed in column 1; county name in column 2; state in column 3; gross
amount column 4; tax amount column 5; there is a county totals after each
county. State total at the bottom

I need to look for the city county listed shown on the "receiving sheet" in
the "data worksheet" match the names then place the gross and tax info form
column 4 & 5 from the "data" into the receiving sheet.

I filtered just the county names and range named them; I did the same with
city. Now, I do not think that is correct because the forumla cannot match
the city with county. There could be 2 cities with the same name but be in
different counties.

I used Vlookup but it if did not find the city/county it returned an N/A.
I cannot total the columns when N/A is present.

How can I change the N/A to blank or zero? Should I range name the city and
county in the format the are currently in?
 
J

JulieD

Hi

the 'problem' as i'm reading it is that in 'receiving data' you have city &
county in one cell, while in 'data worksheet' you have them in different
columns ... therefore, my suggestion would be to insert a new column in
'data worksheet' (which can be hidden) as column C (or A if you really want)
and concatenate the city & county into that cell (=A1 & " " & B1) and use
that as the first column of your table to lookup to.

additionally, i would remove the subtotals from the 'data worksheet' and use
data / sort & then data / subtotals when you need to see the subtotals.

Hope this helps
Cheers
JulieD
 
T

taxmom

Hi Thanks, Unfortunately, the "data" is transformed from the main frame and
it is volumnious. Data changes each month but the format is always the same
The current vlook is working I just can't get the N/A to be blank when the
city/county cannot be found.
 
J

JulieD

Hi

in that case try the following

=IF(ISNA(your_vlookup_formula),"",your_vlookup_formula)

Cheers
JulieD
 
T

taxmom

I can't seem to get it to work right this is a more advance formula than I am
use to working with. My formula is:

-Vlookup("totals for county: autauga", alavlookupbycounty,4,false)

how and where do I incorporate the IF ISNA?
 
J

JE McGimpsey

One way:

=IF(ISNA(VLOOKUP("totals for county: autauga",
alavlookupbycounty,4,false)), "", VLOOKUP("totals for county: autauga",
alavlookupbycounty,4,false))
 
T

taxmom

fantastic!!!!

thank you so much!




taxmom said:
Hi Thanks, Unfortunately, the "data" is transformed from the main frame and
it is volumnious. Data changes each month but the format is always the same
The current vlook is working I just can't get the N/A to be blank when the
city/county cannot be found.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top