D
Damian
Excel Programmers, Geniuses, and Gurus, I seek your counsel. I want to
calculate an "Amount" for each record in my dataset, but I want to do it by
pointing the "Amount" formula to use the contents of a single cell on another
worksheet to build the formula used. I'll try to explain:
I have two sheets "Settings2005" and "Data2005". The table has a variable
number of entries. The calculation occurs in Column H based on the content
of the other fields. A sample from "Data2005" appears as follows:
A C D E F H
7 Date SenderID ClientID Cost Quantity Amount
8 9/30/2004 09548 2809-01 0.83 3 $2.49
9 10/2/2004 00358 9901-01 1.29 1 $1.29
10 10/20/2004 05843 3128-US 0.37 800 $296.00
11 11/5/2004 02276 8614-54 22.50 2 $45.00
But the column labels and the data they contain may change depending on what
is input on "Settings2005". Therefore, I need to be able to declare the
formulas to be used in one location in "Settings2005", and have them
referenced in "Dataset!H:H". Below is the area designated for formulas in
"Settings2005":
A H
19 Relative Labels Master Formula Mappings
20 Amount Locked Map 0 IF(ISNUMBER(E11),E11*F11,IF(ISBLANK(E11),"",10)))
21 Units Map 1 SUM(F8:F265) &" Items"
22 Transactions Map 2 COUNTA(E8:E265)
23 Report Total Map 3 SUM(H8:H265)
24 Custom Map 4 Settings!C24
To prevent the formulas from calculating and returning the result, I removed
the "=". But the problem is this: How can I reference these formulas
(Settings2005) in Data2005, but have relative references which reflect the
location of the cell doing the references?
calculate an "Amount" for each record in my dataset, but I want to do it by
pointing the "Amount" formula to use the contents of a single cell on another
worksheet to build the formula used. I'll try to explain:
I have two sheets "Settings2005" and "Data2005". The table has a variable
number of entries. The calculation occurs in Column H based on the content
of the other fields. A sample from "Data2005" appears as follows:
A C D E F H
7 Date SenderID ClientID Cost Quantity Amount
8 9/30/2004 09548 2809-01 0.83 3 $2.49
9 10/2/2004 00358 9901-01 1.29 1 $1.29
10 10/20/2004 05843 3128-US 0.37 800 $296.00
11 11/5/2004 02276 8614-54 22.50 2 $45.00
But the column labels and the data they contain may change depending on what
is input on "Settings2005". Therefore, I need to be able to declare the
formulas to be used in one location in "Settings2005", and have them
referenced in "Dataset!H:H". Below is the area designated for formulas in
"Settings2005":
A H
19 Relative Labels Master Formula Mappings
20 Amount Locked Map 0 IF(ISNUMBER(E11),E11*F11,IF(ISBLANK(E11),"",10)))
21 Units Map 1 SUM(F8:F265) &" Items"
22 Transactions Map 2 COUNTA(E8:E265)
23 Report Total Map 3 SUM(H8:H265)
24 Custom Map 4 Settings!C24
To prevent the formulas from calculating and returning the result, I removed
the "=". But the problem is this: How can I reference these formulas
(Settings2005) in Data2005, but have relative references which reflect the
location of the cell doing the references?