Extract Cell Reference

Copper Contributor
I have a large spreadsheet that uses a simple formula to put data in a cell.
In cell B2 is. "=b4" which is 25
In Cell A1 is the text "Fr: B4" that I keyed in manually after looking at the formula in B2.


A. B
1 Fr: B4. 25

Now if I insert a row and a column. This puts "25" in row 2, Column C

I have 473 cells like this in my spreadsheet. Each has to be updated manually if structure changes move the data.
I need a the formula in A1 to update the text to display "Fr: C2" so I dont have to search and update changes by hand.

Jim: Fun2av8@gmail.com




I
1 Reply

@Jmcwho01 

Your description is confusing. Is the formula =B4 in B1 or in B2? And do you want A1 to refer to B1/B2 or to B4?

Anyway, you can use a formula like this in A1

="Fr: "&SUBSTITUTE(CELL("address",B4,"$","")

Change B4 to suit your purpose.

When you insert or delete rows and/or columns, the result of the formula will be updated accordingly.