Formula containing a reference to a cell that can change its position

**polocar** · 06-26-2009, 02:35 PM

Hello,
I use Excel 2003, and I have a file called “Cities_Persons.xls” where the first column is populed by names of cities and names of persons in this way (I use R1C1 notation):

cities (R1C1 cell)
madrid (R2C1 cell)
paris (R3C1 cell)
…
london (R14C1 cell)
(empty separation cell, R15C1 cell)
persons (R16C1 cell)
paul (R17C1 cell)
michael (R18C1 cell)
…
john (R25C1 cell)

Unfortunately I can’t change this structure, so I must keep it in this way… Be aware that who fills this file can add/delete cities (and persons too), so the beginning row of the persons’ names (R17C1 cell for the first one in the example above) is not fixed, but can change.

In another file called “Persons.xls” I must automatically write in the first column (starting by R1C1 cell and proceeding on R2C1, R3C1, etc.) only the names of persons (not of cities) that I find in “Cities_Persons.xls” file, so I should obtain:

paul (R1C1 cell)
michael (R2C1 cell)
…
john (R9C1 cell)

I tried to insert in “Persons.xls” R1C1 cell this formula:

=’[Cities_Persons.xls]Sheet1’!R1+MATCH(“persons”;C1;0)C1

hoping that, during the formula processing, Excel understood that it had to calculate the row of the linked cell as 1 + the row of the cell where there is the header “persons” (that is 1 + 16, so the linked cell is R17C1, so the value is “paul”), but Excel it gives me an error (I realized that it doesn’t allow to write formulas like R1+16C1)… I can’t write R17 directly because, as I have already said, the first person name row can change.
Can you suggest me a formula to do that, without using a macro (that should be necessarily executed every time I open “Persons.xls” file)?
Thanks a lot

**DonkeyOte** · 06-26-2009, 03:03 PM

Based on R1C1 setup and locale settings perhaps:

B1: =MATCH("persons";'[Cities_Persons.xls]Sheet1!C1;0)

A1:
=INDEX('[Cities_Persons.xls]Sheet1'!C1,R1C2+ROWS(R1C:RC))
copied down

B1 is used to avoid repetitive calculations - you need only calculate the position of Persons in Column 1 in the other file once... repeating the function in each cell in A would not make sense in terms of optimisation.