I have an excel sheet used to reformat data coming in from an Access DB that then gets fed back into ESRI's ArcMap. All is working fine, except that the worksheet will need to load data from different Access DB's at different times (not simultaneously) and sometimes the Access DB's have different #'s of records which mis-align the reformatting sheet.
For example, DB1 has 4 records; DataValueA, DatavalueB, DatavalueC, DataValueD. DB2 only has 3 records; DatavalueA, DatavalueC, DatavalueD (missing DatavalueB because it does not apply for that DB).
When I set up my reformatting worksheet tab to see DB1 I lay out my columns to show DataValueA to DataValueD, linking it to the data source worksheet tab showing the Access file contents as 4 rows of data values. BUT then I load in DB2 which comes in with only 3 rows on the Access Source tab, which means a reformatting tab cell that referred to cell A4 in DB1 source tab would now have to refer to cell A3 in DB2 source tab.
To get around this I am wondering if there is any way to associate a name or criteria with the source data worksheet cells so that the reformatting worksheet can make reference to those instead of cell addresses? ie. "reformatting cell = DataSourcePolygonArea IF ((CruiseType = 'Type1') & (Method = 'ground'))" - meaning the reformatting cell would look to the source data and find a row where CruiseType and Method were valid and then use the DataSourcePolygonArea from that row.
I know, convoluted explanation, but really I am just trying to figure out how to find certain information in a worksheet when it is not in a stable cell reference. Ultimately I will have to verify values in two columns are true to return a value in a third column, but trying to keep this simpler to get the basics figured out (ie. if A1="value 1" AND B1="value 2" give me the value for C1)
Here's a pic of some example data to further explain, and here is a link to a spreadsheet with the example: