Combining index, address, reference-type formulae

**cmboulter** · 12-16-2011, 12:06 PM

Hi,

Please see the attachment.

The file is a combination of several txt files, each one with the same header (of 4 lines), and the same format.

The purpose of the sheet is to calculate the offset in the X and Y direction from the origin. This is calculated by subtracting each row's values from the origin. If I just did it for one, I could obviously use the $ to tell the formula to keep the cell reference static.

However, with several of these combined files (there are 160 in my real file), it wouldn't work as I would need to move the static cell reference each time I moved past a new header (onto a new object).

Is there some way to use the row reference to make the static reference stay on the first row of each dataset? (i.e. at the bottom of each header, and at the first XY point)

Please ask if you need clarification, its hard to explain!

**tigeravatar** · 12-16-2011, 12:40 PM

cmboulter,

In cell D1:
=IF($A2="ORIGIN",IF(COLUMN()=4,"Xoffset","Yoffset"),IF(OR($A1="ORIGIN",ISNUMBER($A1)),LOOKUP(2,1/($A$1:$A1="ORIGIN"),B$1:B1)-B1,""))

Then copy to cell E1, then copy down

**TMS** · 12-16-2011, 12:54 PM

Maybe:

=IF(AND(A5<>"Header row 1",A5<>"Date"),IFERROR(INDIRECT("$B"&MATCH("Building "&COUNTIF($A$1:A5,"ORIGIN"),B:B,0)+3)-INDIRECT("B"&ROW(B5)),""),"")

for the X offset.

Similar for the y offset.

Regards, TMS

**cmboulter** · 12-19-2011, 08:55 AM

Perfect!

Thank you!

**TMS** · 12-22-2011, 11:27 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save