What Functions should be used

[taxmom]

1stworksheet
City/Couty Gross Tax

county name (formula) Formula
city Name (formula Formula

2nd worksheet (data)
City name County Name Gross amount tax amount

I need to match the city county name from worksheet 1 with the city county
name in worksheet 2 and then bring over from the data worksheet the amounts
from column 4 & 5.

What would I use? I have a Vlookup but it is only searching for the city
name, and I need both. How would I link the search for city and the search
for county?

I have not been able to come up with the right formula to make this work. I
have been trying for days.

 

[JulieD]

Hi Taxmom

as per my original reply to your last thread - from my understanding of your
question you're going to need to insert a new column into the 2nd worksheet
either before the city name or after the county name and concatenate these
two fields so that they match what you have in the first column of your 1st
worksheet ... you can then use this column as the first column in your
VLOOKUP table and return the information from the gross & tax amount fields
e.g.
if you have in 1st worksheet
city/county....gross....tax
Perth/WA....=IF(ISNA(VLOOKUP(A1,Data!$C$2:$E$1000,2,0)),"",VLOOKUP(A1,Data!$C$2:$E$1000,2,0))
(and a similar formula for tax)

and in your second worksheet
city....county...NewColumn....gross....tax
Perth...WA.....=A2&"/"&B2..1000.....20

does this work?

Cheers
JulieD

 

[taxmom]

Thanks,

This sounds like it will work the only problem is that Worksheet 2 is a copy
from the main frame. The copy comes over the same each month just data
changes. So, to create a new row I would need to create it in each state
worksheet AL - WY

The name that comes over in worksheet 2 in the city column says "Total for
city: agawam". In worksheet 1 it will only show the city name. In the
Vlookup I have added "total for city: agawam". I'm not referencing a cell
from woksheet 1 I'm actually typing what name to find. Each state city will
be different. Worksheet 1 has approx 8,700 lines. I'm trying to avoid
adding new lines to either worksheets because of the volumn in both
worksheets. I'm just not finding an easy way to do this.

If I add the column I will need to create a macro so that each month when
the data is pulled over it can create a new column and pull the city county
names together in the column.

Thank you for the help. I didn't want to have to go down that path. But, I
think I might have to.

thanks! : )

 

[JulieD]

Hi taxmom

if all of the state workbooks are set up the same way (ie the columns in the
same order), you can group the sheets (click on AL, hold down SHIFT & click
on WY) THEN insert the column, type the formula, double click on the
autofill handle and all of the sheets will get this combined column all at
once - which shouldn't take too long!

however, someone else might have a better idea on how to achieve what you're
after.

Cheers
JulieD

 

[taxmom]

Your right that will help speed up the process

thanks