Reletive Referencing of Another Cell's Formulas

[Guest]

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?

 

[Guest]

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 that’s helpful and not insulting your intelligence

 

[Guest]

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

 

[Guest]

Dan,

I've considered a CASE statement, but the problem is that the case
statements I was able to come up with implied a limited predetermined
criteria regarding the formula to be used.

The entire idea of referencing the "formula text" is that it should be able
to take *ANY* possible valid formula and use it in the second worksheet's
summary column. Since I can't predicted what any given user might input as
the formula, I'm not sure how I could code in all the possible
exceptions/outcomes.

As for my intellegence, it is rather insulting, isn't it? ;-) Not to
worry, I come here and openly announce my failure to overcome this obstacle,
so I'm not worry about how (to borrow from Scott Adams) "untelligent" that
makes me appear.

Damian