Here’s the deal: I have 2 workbooks. One is basically a template (we’ll call it “main”), the other is replaced monthly with updated data (we’ll call it “data”).
In main, I have a sheet with a row for political jurisdictions, and other rows for total building permits pulled, average cost listed on the permits, etc. I have one of these sheets for each county we deal with (4).
The other book, Data, has one sheet and is basically the exact same as the sheet I just described in main, with one difference: the jurisdictions are in a different order. Also, all 4 counties are in 1 sheet in data. it has a column to indicate the county.
all i need to do is find the jurisdiction from main in data and plug in number of permits, average cost, etc back into main. This wouldn’t be a problem if there weren’t the same jurisdiction name in more than 1 county. So, i need a formula that will look at county AND jurisdiction, then get the corresponding offset cell data. Any thoughts?
I could do this fairly easily in VBA, but I’d much rather have it as a formula.
The easiest thing to fo, I would htink would be to create a unique identifier for each rcord compoed of something like the first five letters of the county and the district name. So a county WILLIAMS and a district 5 then the identifier would be WILLI5. In the main you would then use the LEFT comand in the lookup function.
That is what I would do also. Just add a new column to the data sheet which combines county and district names. Some examples:
Of course, then in the VLOOKUP formula, you’d have to manipulate the search criteria the same way to get a unique match.
I’m not familiar enough with the MATCH formula to discuss it - but I think that it’s not enough to use it on its own to get want you want - you could probably combine it for a complex lookup function.
I think this might give you what you need in one formula without having to add a column to your data spreadsheet. It is pretty much the same idea mentioned above but the concatenation occurs in the formula so you don’t need a seperate column.
Using named ranges will make this clearer in my head, so assume on your main sheet you have a range for county (“main_cty”) and jurisdiction (“main_jur”) and on your data sheet you also have a range for county (“data_cty”) and jurisdiction (“data_jur”) as well as ranges for each of the columns you want to reference (“data_permits” for example). Then:
will give you the data in the “permits” column where the combination of the county/jurisdiction on the data sheet matches the combination of county/jurisdiction on the main sheet.
Just keep in mind that this is an array formula so you need to Ctrl+Shift+Enter to get the {} brackets.
Whoops. I screwed that up. You don’t want to use the whole range on the main sheet in the formula. You just want to use the combination of two specific cells. So:
Well, I decided to go about this a different route, though I do appreaciate all the help. But now I have another issue.
I’m running Windows XP Pro and Excel 2003
I have the formula my Main book: =SUMIF(‘H:\Data\Share\Build_Fax\Geo_Summary[thisyear.xls]thisyear’!$E$7:$E$32,$A9,‘H:\Data\Share\Build_Fax\Geo_Summary[thisyear.xls]thisyear’!F$7:F$32)
and it returns: #VALUE!
unless i change it to:
=SUMIF(thisyear.xls!$E$7:$E$32,$A9,thisyear.xls!F$7:F$32)
For some reason it doesn’t like the directory being included, which wouldn’t be a problem, but Excel put the directory info there itself!!
The ThisYear book is created in Foxpro and opened in VBA (via the Main workbook). Initially I had: =SUMIF(thisyear.xls!$E$7:$E$32,$A9,thisyear.xls!F$7:F$32) as my formula and it worked great, but the next time I started from scratch, had Main open ThisYear and all of a sudden there is directory info in my formula.
I think the problem is that it’s trying to read the data from a worksheet called ‘thisyear’ - is the sheet you’re pulling data from really called that?
Otherwise you have to change ‘thisyear’ that shows up after the filename in brackets to reflect the actual sheet-name.
