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?

Damian,

Well I'm not totally sure I understand the problem, but it doesn't sound
like it would be to hard to do with a macro. The first step I would do would
be to record a macro of you doing what you want to do, then hit "alt-F11" and
that will take you to the VBA editor where you can modify and expand upon the
macro code to include necessary logic statements etc.

I would sujest a case statement to select your equations
ie
select case Varible


Hope thats helpful and not insulting your intelligence

"Damian" wrote:

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?

case x
Code
case y
Code
case z
code
case else
code
end select

"Damian" wrote:

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?