3

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: example data

  • That's what INDIRECT is for, into INDIRECT(range) you can build the range name any way you want, so INDIRECT(if(thing, range1, range2)) will do the trick – fernando.reyes May 5 '17 at 18:58
  • Or VLOOKUP, or INDEX/MATCH – Scott Craner May 5 '17 at 19:11
  • I looked into INDIRECT and understand what it does, but not how i can use it in my situation. I have two criteria to confirm before picking the cell to use the value from, but again that cell reference might change, so don't know how this works. Can you explain further? One detail is that there will be multiple rows with criteria 1 and criteria 2 but only one row that has the proper combination of criteria 1 & 2 together, which is the cell value I want to have returned. – user25644 May 5 '17 at 20:52
  • 1
    I have part of it working, but not able to have it look through and array for the values to match. I have the following formula: =ADDRESS(MATCH(IF(AND(D8="Type 2",A8="method 1"),C8, "NO"),C1:C28,0),COLUMN(C2),4). If I change the text to find (ie. "Type 3") I get a #N/A error, which makes sense as D8 does not say "Type 3", but how can i make it so that D8, A8, are a range of rows, and that C8 becomes the valid row of column C once the criteria are met? – user25644 May 5 '17 at 22:25
  • 1
    Switched the formula around to this: =ADDRESS(AND(MATCH("Type 2",D1:D28,0),MATCH("method 1",A1:A28,0))+ROW(C2)-1,COLUMN(C2),4). Seems closer but still not right... – user25644 May 5 '17 at 22:48
2

As Andre mentioned, used named cells (or range) instead of indices.

  1. On the cell, right click and name -> DATA1
  2. On the second cell, right click and name -> DATA2
  3. On the cell that refers to the data, say =SUM(DATA1, DATA2)

Now when you move the particular data item to another cell, you will find that the SUM cell still has the correct amount.

| improve this answer | |
  • The hitch in this plan is that the incoming source cells change in structure, as they are coming from an external source that is not always consistent. So I need to be able to identify the cells based on the incoming database cell. – user25644 Jun 29 '17 at 22:45
  • For example, I could have a 2 databases called "EndangeredSpecies", each database for a separate region. The first record in "Area A"'s EndangeredSpecies DB is "wildlife" second record is "plants", third record is "marine". However in "Area B" the field crew started recording marine species first, so record 1 is "marine" record 2 is "plants", and three is "wildlife". When excel reads cell A1 from Area A it gets the value for "wildlife" but if I load the DB for Area B cell A1 is "marine". – user25644 Jun 29 '17 at 22:46
  • I need to be able to aggregate all the "wildlife", "marine", and "plant" records into a single worksheet row/record for re-joining into a database, in essence forcing a formatted attribute structure in sheet 2 on the data existing in sheet 1, when sheet 1 is volatile. – user25644 Jun 29 '17 at 22:48
0

Make the reference a Named Range

Set the named range to an INDEX MATCH combination that returns you the right DataSourcePolygonArea.

If you provide us with some sample data I can put together a specific example.

| improve this answer | |
  • Added both a pic and link to an example spreadsheet – user25644 Jun 29 '17 at 23:46
  • Is there an excel spreadsheet example I can download somewhere to show me how this works? I am not understanding it from the answers given. Thanks. – user25644 Sep 20 '17 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.