Dynamic cell reference in formula

[screamer31]

I have a basic formula like G9-C17 but problem is the one of the reference cells is supposed to get changed everyday. In the row17, new column data will be appended everyday. Hence the formula would go on like G9-D17, G9-E17 and so on, changing daily. Any idea how make this dynamic reference?
P.S. I tried with Indirect but didn't seem to work

 

[bosco_yip]

Please post out of your formula, and explain how do you want to make it as dynamic reference ?

Please also upload a sample file with around 10 lines raw data and the expected results

Regards

 

[Peter Bartholomew]

I tend to use a Table for input data. The structured reference that defines the table is dynamic.
= INDEX( Data, ROWS(Data), COLUMNS(Data) )
then references the final cell of the right-most column.

 

[pecoflyer]

Something like =G9-INDEX($a$17:$aa$17,match(9.9999e+307,$a$17:$aa$17))

 

[Peter Bartholomew]

@pecoflyer
That would do it (at least until someone forgets and jots their lottery numbers to the right).
Not for me though, I trap all direct cell references as errors !