I have constructed a table of information for multiple workplaces. Each
workplace has data common to them all, e.g..
Project1 Project2
Data1 Data2 Data3 Data1 Data2 Data3
Workplace 1 3 2 2 7 6 5
Workplace 2 10 10 9 6 6 6

I have multiple formulas that give me averages and other info for eac
specific workplace and for all of our workplaces.
In the example above, the formulas would use B3, C3, D3 and so on for
Workplace 1.
I want the formulas to continue to extract data from these specific cells
but yet I want to add a new project by inserting cells for workplace1 for
project1 and shifting the cells to the right. The "old" project1 data
becomes project2 data and project2 data just shifts to the right as the
oldest historical data.
My problem is that when I insert cells (even if I use $B3, $C3, etc.), my
formulas continue to look at the old data (now Project2).
Is this just a shortcoming of Excel or am I doing this in an inefficient
manner?
Thank you

Not sure if this is the best way, but I did this by replacing the range with
INDIRECT("RANGE").

For example, to sum project 1 data 1 (column B) for both workplaces, with
the data in B3 and B4, =SUM(INDIRECT("B3:B4"))

Hope that suits your needs.

"JohnW" wrote:

I have constructed a table of information for multiple workplaces. Each
workplace has data common to them all, e.g..
Project1 Project2
Data1 Data2 Data3 Data1 Data2 Data3
Workplace 1 3 2 2 7 6 5
Workplace 2 10 10 9 6 6 6

I have multiple formulas that give me averages and other info for eac
specific workplace and for all of our workplaces.
In the example above, the formulas would use B3, C3, D3 and so on for
Workplace 1.
I want the formulas to continue to extract data from these specific cells
but yet I want to add a new project by inserting cells for workplace1 for
project1 and shifting the cells to the right. The "old" project1 data
becomes project2 data and project2 data just shifts to the right as the
oldest historical data.
My problem is that when I insert cells (even if I use $B3, $C3, etc.), my
formulas continue to look at the old data (now Project2).
Is this just a shortcoming of Excel or am I doing this in an inefficient
manner?
Thank you