Reference Specific Cell Location - Don't Change Formula If Row Added

Question

Hello - 

How can I reference a specific cell and not have the value change if a row is added? The cells are on the same sheet. See below. Example 1 is the base case. When a row is added in Example 2, the formula in A12 updates from =A4 to =A5. However, I don't want the formula in A12 to update. I want it to continute to reference A4 as shown in Example 3. Furthermore, I'd like to be able to drag the formula down the column so that I can grow the bottom list as rows are added to the top list. I've tried INDEX but the formula doesn't update when dragged. Thanks for the help!

Answer 1

Hi JFB. I am an Excel user like you.

I have had to deal with this problem before. I get around it as NLtL indicated by dragging it without the quotes and then using Replace to fix the quotes. When you drag the formula it may error with #REF, that's OK.

But, in order to edit the formula with Replace you must first fix it so that Excel doesn't recognize that it is a formula or it won't let you edit it in pieces like you need to. I do this by changing the = sign at the beginning of the formula to something random that I can change back when finished. It must be something that cannot appear anywhere else so I usually highlight the column (or formulas) and use Replace "=" with "red=" and click Replace All. Now The string doesn't begin with the = sign so Excel doesn't treat it as a formula.

Now use Replace to replace the ( with (" and then replace the ) with "). When you have those complete replace all of the "red=" with = sign again and you are back in business.

Reply if you have additional questions or more information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Re: cannot drag down

I pointed that out, in my 1st response, that a string will not fill.
My only idea now is enter an Indirect formula (without quote marks), fill it down then go back in and add quote marks to all of them.

'NLtL https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

Answer 3

Re: "don't change formula if row inserted"

For Cell A12 use = Indirect("A4")
It uses a string as the reference, so it won't fill.

If you want to sum a range of fixed cells then...
=SUM(INDIRECT("A4:A12"))

'---

NLtL https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O Add_Table of Contents, Calculate Payments, Custom_Functions, Professional_Compare

Answer 4

Thank you for the response. However, I cannot drag the formula to expand. If I use =INDIRECT("A4") for cell A12 and drag down, cells A13 & A14 also reflect =INDIRECT("A4"). How can I make them look like below without manually entering in every cell? Is that possible? Or would another formula make more sense here? Thanks again!

Answer 5

Rich~M & NLtL - thank you for the help! Not ideal having to go back and add the quotation marks but better than nothing! These community boards are a great resource. I'm thankful to you both for sharing your knowledge. Have a good one!