If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I want multiple formulas to always extract from same cell(s)
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 |
#2
|
|||
|
|||
I want multiple formulas to always extract from same cell(s)
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 |
Thread Tools | |
Display Modes | |
|
|