| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
| Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
| Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions | |||
| Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
Hybrid Mode
