Excel 2007 : Getting a cell reference used in a formula

[Evil Emma]

I am using an index formula, but would like to return the cell reference used in that formula into another cell. Any ideas?

[DonkeyOte]

Not very clear I'm afraid - if you need to access a hard wired parameter from the other formula then you will be looking at VBA.

Could you elaborate by means of example(s) ?

[Evil Emma]

This is the formula I am using:

=INDEX('bfp pivot'!7:7,MAX(COLUMN('bfp pivot'!7:7)*('bfp pivot'!7:7<>"")))
What I would like to do is set up another cell next to it that tells me what cell the index formula used to return the result....

Ps - thanks for the swift reply....

[teylyn]

try

=cell("address",INDEX('bfp pivot'!7:7,MAX(COLUMN('bfp pivot'!7:7)*('bfp pivot'!7:7<>""))))

[Andy Pope]

Array formula, commit with CTRL+SHIFT+ENTER

=ADDRESS(7,MAX(COLUMN('bfp pivot'!7:7)*('bfp pivot'!7:7<>"")))

[DonkeyOte]

Or, yet another alternative might be:

=ADDRESS(7,LOOKUP(2,1/('bfp pivot'!7:7<>""),COLUMN(7:7)),,,"bfp pivot")
confirmed with Enter

If the data type you're looking for in row 7 is constant (number, text etc...) you can simplify further.