Cell references

Posted on 2011-04-26
Last Modified: 2013-11-05
Gents and Ladies,

I'm a little bit embarrassed to ask such a seemingly easy question, but I have somewhat of an issue that is really getting under my skin here.

In one cell I have a straightforward and simple formula (For example: =(F2+G2+H2)/2))

My problem is that when i insert a a column the formula changes to =(G2+H2+I2)/2 regardless of the cell reference being absolute or not.  I would like the formula to always refer to the same cell no matter if I insert a column or not. (In other words, the formula to remain unchanged)

I'm absolutely stymied by this.

Please help.


Thank you.
Question by:thirtywinter
    LVL 30

    Expert Comment

    For that you have to use Offset formula for example if the above formula is in B2 then you can also use the following


    LVL 30

    Accepted Solution

    Oops I forgot the /2


    Similarly if the formula is in A2 then change it to


    LVL 9

    Expert Comment

    You can also use the INDIRECT function of Excel



    Author Closing Comment

    Worked like a champ! Thank you very much!

    I was starting to wonder if I was loosing my mind or something.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now