EXCEL FORMULA
 

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?

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


"Taxmom" wrote in message
...
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?

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.

"JulieD" wrote:

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


"Taxmom" wrote in message
...
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?

Hi

in that case try the following

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

Cheers
JulieD

"taxmom" wrote in message
...
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.

"JulieD" wrote:

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


"Taxmom" wrote in message
...
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?

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?



"JulieD" wrote:

Hi

in that case try the following

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

Cheers
JulieD

"taxmom" wrote in message
...
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.

"JulieD" wrote:

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


"Taxmom" wrote in message
...
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?

One way:

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

In article ,
taxmom wrote:

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?

fantastic!!!!

thank you so much!




"taxmom" wrote:

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.

"JulieD" wrote:

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


"Taxmom" wrote in message
...
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?