Extract Cell Reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2904348%22%20slang%3D%22en-US%22%3EExtract%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2904348%22%20slang%3D%22en-US%22%3EI%20have%20a%20large%20spreadsheet%20that%20uses%20a%20simple%20formula%20to%20put%20data%20in%20a%20cell.%3CBR%20%2F%3EIn%20cell%20B2%20is.%20%22%3Db4%22%20which%20is%2025%3CBR%20%2F%3EIn%20Cell%20A1%20is%20the%20text%20%22Fr%3A%20B4%22%20that%20I%20keyed%20in%20manually%20after%20looking%20at%20the%20formula%20in%20B2.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EA.%20B%3CBR%20%2F%3E1%20Fr%3A%20B4.%2025%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20if%20I%20insert%20a%20row%20and%20a%20column.%20This%20puts%20%2225%22%20in%20row%202%2C%20Column%20C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20473%20cells%20like%20this%20in%20my%20spreadsheet.%20Each%20has%20to%20be%20updated%20manually%20if%20structure%20changes%20move%20the%20data.%3CBR%20%2F%3EI%20need%20a%20the%20formula%20in%20A1%20to%20update%20the%20text%20to%20display%20%22Fr%3A%20C2%22%20so%20I%20dont%20have%20to%20search%20and%20update%20changes%20by%20hand.%3CBR%20%2F%3E%3CBR%20%2F%3EJim%3A%20Fun2av8%40gmail.com%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2904348%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2904421%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2904421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200882%22%20target%3D%22_blank%22%3E%40Jmcwho01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20description%20is%20confusing.%20Is%20the%20formula%20%3DB4%20in%20B1%20or%20in%20B2%3F%20And%20do%20you%20want%20A1%20to%20refer%20to%20B1%2FB2%20or%20to%20B4%3F%3C%2FP%3E%0A%3CP%3EAnyway%2C%20you%20can%20use%20a%20formula%20like%20this%20in%20A1%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%22Fr%3A%20%22%26amp%3BSUBSTITUTE(CELL(%22address%22%2CB4%2C%22%24%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EChange%20B4%20to%20suit%20your%20purpose.%3C%2FP%3E%0A%3CP%3EWhen%20you%20insert%20or%20delete%20rows%20and%2For%20columns%2C%20the%20result%20of%20the%20formula%20will%20be%20updated%20accordingly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
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.