Referencing Values in cells to be used as Named Formulas

[Lorem Ipsum]

Hello and thanks for your attention,

I'm attempting to create a table with an auto-calculated formula column for some numbers, which has a different set of rules for several types of such formulas.

Let's assume that Column A has the "Type", Column B has the original number and Column C has the formula.
For arguments sake, Let's say that All Oranges need to have an applied calculation of 3/5, while all Grapefruits need a calculation of 4/7.

Hence, in C1 I should type

If(A1="Oranges",Sum(B1*3/5),If(A1="Grapefruits",Sum(B1*4/7),B1))

(This is off the top of my mind, forgive the possible parentheses error. This example should do the appropriate calculation for Oranges & Grapefruits and return the same amount as in B1 for anything else).

As seen above, this already requires nested IFs for 2 types of formulas; I have several, and actually need two formulas for each row - Say C1 should do calculation type 1 for Oranges and D1 should do Calculation type 3 on the result received in C1, While C1 should apply calculation type 2 for Grapefruits and still calculation type 3 on D1, but on the original number found in B1 - well, it gets kinds complicated for nested ifs at this stage.

My initial thought was to make a table of reference formulas for each type, matching all against a 'template number' (or Test number); Then matching the type (A1) via a VLookup table or Match/Index to the 'Template' table, pointing to the template-calculation

However, once the actual calculation sheet did the reference, the result was given as whatever was in the template table, of course. Hence, if my test-number was '10' and the result for Oranges was '6', in the final result table it would put in '6' no matter what was the given number in B1 - The Index/Match pointed to the template cell, and that, of course, included the template result.


While this approach would have been the simplest for me (having a nice table where I could edit the actual formulas in a neat manner), it occured to me that I may need to resort to Named Formulas. So, Set two Named Formulas, one called 'Oranges' and the other 'Grapefruits', the first being =!$B2*3/5 and the second =!$B2*4/7 [1], and now I'd like to use those - however, again, I'd like the formula Name to be chosen by a VLookup table: If my new method leads me to a new nested =IF(A1="Oranges",Oranges,IF(A1="Grapefruits",Grapefruits,)) then once again we're dealing with nested IFs which will grow impractical once I get to Watermelons and ridiculous when it's time for Pomgrenades.

Naturally, I cannot call the result formula-cell from the Template table, for the same reason stated above; I need a reference column. To keep matters simple, we'll regard the Type column also as the Names column [2]. So the idea is that in C1, I should put something like

=Index(Types,Match($A1,Types,0))

So it Matches the Text of A1 to the list, and uses the [same, in this example] Text as the Named Formula for the cell.

And here is where I fail.

Although if I enter Manually into a cell =Grapefruits it references the Named Formula alright, once I do it as described above, the result is being put there as Text. And I could not figure out how to turn that text into something Excel will regard as a formula - enclosing the above formula in a Value() didn't work, Preceding it with a "=", in-and-out-of-Value() didn't work, and it all seems a bit illogical to me in the first place - after all, I don't want it to be regarded as a Value in the first place, but as a Formula.


Any thoughts on the matter will be greatly appreciated.


Many thanks,

-- Lorem Ipsum


Obligatory version info: Excel 2003, Windows XP

[1] Actually I used a reference building the $Xn address from Indirect(Address(Row(),etc.)). Although using !$Xn would have been much simpler and faster, as portrayed in ExcelIsFun's Name Trick #11, however I found it to not updated the result automatically upon changing the source cell. Using an equivalent cell reference in a Name, but building the address "The hard way" using derived ADDRESS() from ROW(), COLUMN() and arithmetic calculations, behaves as expected.

[2] Although I made separate two columns - the 'Type' column which has Human-languages types, sometimes with Spaces; and a separate 'Formula Name' column referred to via VLookup. This is more then cosmetics; as mentioned above, different types of formulas may be used in different columns for the same type - this should make it easier to refer to one or the other.

 

[jasonb75]

Welcome to the board!

Based on the information you have given using a lookup table should work.

Sheet5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 85px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>lookup table</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>oranges</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD><TD>Oranges</TD><TD style="TEXT-ALIGN: right">0.6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>grapefruits</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">2.857143</TD><TD> </TD><TD>Grapefruits</TD><TD style="TEXT-ALIGN: right">0.571429</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>bananas</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2.5</TD><TD> </TD><TD>Bananas</TD><TD style="TEXT-ALIGN: right">0.833333</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>bananas</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">6.666667</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>apples</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">10</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>oranges</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2.4</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=IF(ISERROR(MATCH(A2,$E$2:$E$4,0)),B2,B2*VLOOKUP(A2,$E$2:$F$4,2,0))</TD></TR><TR><TD>F2</TD><TD>=3/5</TD></TR><TR><TD>C3</TD><TD>=IF(ISERROR(MATCH(A3,$E$2:$E$4,0)),B3,B3*VLOOKUP(A3,$E$2:$F$4,2,0))</TD></TR><TR><TD>F3</TD><TD>=4/7</TD></TR><TR><TD>C4</TD><TD>=IF(ISERROR(MATCH(A4,$E$2:$E$4,0)),B4,B4*VLOOKUP(A4,$E$2:$F$4,2,0))</TD></TR><TR><TD>F4</TD><TD>=5/6</TD></TR><TR><TD>C5</TD><TD>=IF(ISERROR(MATCH(A5,$E$2:$E$4,0)),B5,B5*VLOOKUP(A5,$E$2:$F$4,2,0))</TD></TR><TR><TD>C6</TD><TD>=IF(ISERROR(MATCH(A6,$E$2:$E$4,0)),B6,B6*VLOOKUP(A6,$E$2:$F$4,2,0))</TD></TR><TR><TD>C7</TD><TD>=IF(ISERROR(MATCH(A7,$E$2:$E$4,0)),B7,B7*VLOOKUP(A7,$E$2:$F$4,2,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[tusharm]

I stopped reading after you assumed, argued, and said about C1 and D1. It might help if you posted your actual problem rather than abstractions that yield solutions that may or may not translate back to your actual problem.

As far as C1 and D1 go, the typical formula limit, if any, applies to a single cell and not to the number of nesting levels across 2 cells.

Hello and thanks for your attention,

I'm attempting to create a table with an auto-calculated formula column for some numbers, which has a different set of rules for several types of such formulas.

Let's assume that Column A has the "Type", Column B has the original number and Column C has the formula.
For arguments sake, Let's say that All Oranges need to have an applied calculation of 3/5, while all Grapefruits need a calculation of 4/7.

Hence, in C1 I should type

If(A1="Oranges",Sum(B1*3/5),If(A1="Grapefruits",Sum(B1*4/7),B1))

(This is off the top of my mind, forgive the possible parentheses error. This example should do the appropriate calculation for Oranges & Grapefruits and return the same amount as in B1 for anything else).

As seen above, this already requires nested IFs for 2 types of formulas; I have several, and actually need two formulas for each row - Say C1 should do calculation type 1 for Oranges and D1 should do Calculation type 3 on the result received in C1, While C1 should apply calculation type 2 for Grapefruits and still calculation type 3 on D1, but on the original number found in B1 - well, it gets kinds complicated for nested ifs at this stage.

My initial thought was to make a table of reference formulas for each type, matching all against a 'template number' (or Test number); Then matching the type (A1) via a VLookup table or Match/Index to the 'Template' table, pointing to the template-calculation

However, once the actual calculation sheet did the reference, the result was given as whatever was in the template table, of course. Hence, if my test-number was '10' and the result for Oranges was '6', in the final result table it would put in '6' no matter what was the given number in B1 - The Index/Match pointed to the template cell, and that, of course, included the template result.


While this approach would have been the simplest for me (having a nice table where I could edit the actual formulas in a neat manner), it occured to me that I may need to resort to Named Formulas. So, Set two Named Formulas, one called 'Oranges' and the other 'Grapefruits', the first being =!$B2*3/5 and the second =!$B2*4/7 [1], and now I'd like to use those - however, again, I'd like the formula Name to be chosen by a VLookup table: If my new method leads me to a new nested =IF(A1="Oranges",Oranges,IF(A1="Grapefruits",Grapefruits,)) then once again we're dealing with nested IFs which will grow impractical once I get to Watermelons and ridiculous when it's time for Pomgrenades.

Naturally, I cannot call the result formula-cell from the Template table, for the same reason stated above; I need a reference column. To keep matters simple, we'll regard the Type column also as the Names column [2]. So the idea is that in C1, I should put something like

=Index(Types,Match($A1,Types,0))

So it Matches the Text of A1 to the list, and uses the [same, in this example] Text as the Named Formula for the cell.

And here is where I fail.

Although if I enter Manually into a cell =Grapefruits it references the Named Formula alright, once I do it as described above, the result is being put there as Text. And I could not figure out how to turn that text into something Excel will regard as a formula - enclosing the above formula in a Value() didn't work, Preceding it with a "=", in-and-out-of-Value() didn't work, and it all seems a bit illogical to me in the first place - after all, I don't want it to be regarded as a Value in the first place, but as a Formula.


Any thoughts on the matter will be greatly appreciated.


Many thanks,

-- Lorem Ipsum


Obligatory version info: Excel 2003, Windows XP

[1] Actually I used a reference building the $Xn address from Indirect(Address(Row(),etc.)). Although using !$Xn would have been much simpler and faster, as portrayed in ExcelIsFun's Name Trick #11, however I found it to not updated the result automatically upon changing the source cell. Using an equivalent cell reference in a Name, but building the address "The hard way" using derived ADDRESS() from ROW(), COLUMN() and arithmetic calculations, behaves as expected.

[2] Although I made separate two columns - the 'Type' column which has Human-languages types, sometimes with Spaces; and a separate 'Formula Name' column referred to via VLookup. This is more then cosmetics; as mentioned above, different types of formulas may be used in different columns for the same type - this should make it easier to refer to one or the other.

 

[Lorem Ipsum]

Many thanks to jasonb75 for the quick reply.

Yes, this example even goes beyond what I tried to achieve, with the use of ISERROR; Stripping this down it works pretty much the same:

Excel Workbook
	A	B	C
1	Oranges	10	6
2	Grapefruits	10	5.714286
3	Bananas	10	8.333333
4	Bananas	10	8.333333
5	Apples	10	#N/A
6	Oranges	10	6
7
8
9
10
11	Formulas
12	Oranges	0.6
13	Grapefruits	0.571429
14	Bananas	0.833333
Sheet1

However, I'm afraid this will not suffice what I'm trying to do.

Excel Workbook
	A	B	C	D	E	F
1		Initial Number	Result1	Result2
2	Type1	1000	800	200		For this type, Result1 should be Initial Number / 1.25, and Result2 should be Result1*0.25 (or Result1-Result2)
3	Type2	1000	200	50		For this type, Result1 should be Initial Number / 1.25 / 4, and Result2 should be Result1*0.25.
4	Type3	1000	250	0		For this type. Result1 should be Initial Number / 4, and Result2 should be 0 (always)
5	Type4	1000	640	533.33		For this type, Result1 should be (Initial Number - 200) / 1.25, and Result2 should be (Initial Number / 1.25) * 2/3
Sheet2

In this situation, I cannot use a straight formula of [Initial Value] * [Something]. My problem is not only that sometimes it takes Initial value and Sometimes Result1 as the first argument, depending on the type - that may still be overcome by defining two formula-columns in the lookup table - but also that it's not always the Value
[*]. It's sometimes Value [/] something, sometimes Value [-] something. Hence I resorted to using named [complete] formulas for each type, and using a named reference as their values.

That is, Most formulas intended for use in the Result2 column may have Result1 as their initial value, but some may still use InitialNumber as their first value. Most will be Result1*something, some may be Result1-something.

Basically, the problem begins when the formulas themselves differ, not only the data within. I guess it's sort of determining if the formula needs addition or subtraction depending of whether the type is a "deposit" or "withdraw", but with more variations of types and initial operators.


Hopefully I made some sense.

-- L. Ipsum

 

[jasonb75]

Failproof version

Excel Workbook
	A	B	C	D	E	F	G
1			Result 1	Result 2		lookup table
2	oranges	10	8	2		Oranges	Type1
3	grapefruits	5	1	0.25		Grapefruits	Type2
4	bananas	3	0.75	0		Bananas	Type3
5	watermelons	1000	640	533.333		watermelons	Type4
6	apples	10	2	0.5		apples	Type2
7	oranges	4	3.2	0.8
Sheet5

 

[Lorem Ipsum]

Yes, while this expands the scope of decisions, it also falls straight to the trap I portrayed earlier: Multiple nested IF statements.
Which is exactly what I am trying to avoid.

Multiple IF statements require too many manual tweaking points, too many repetitions even though we adopt the "do it once, copy to them all" approach. Once something changes (either in the formulas themselves, and of course in the sheer number of said formulas) one needs to manually analyze, change and add to the formula. Also, according to what I read, there's a limit of 7 nested IF statement. And I never said I have only 4 such formulas.

Anyway - when I posted my first post I was under the assumption that the EXCEL=>HTML thingy was currently unavailable, and hence I should not use it. After your reply, I thought to actually google for it instead of relying only on what was stated in the forum guidelines. So now I may be able to provide a bit more insight as to my approach (and the method I'm using has a part in what I'm trying to achieve, aiming at both flexibility and readability), what I expect, what goes wrong and exactly what I would like for it to do.

Back to the example:
I'm assuming an initial "Amount" of 1000 units for each category.
I'm also naming some standard 'set numbers'. Again, I would prefer not to use them in the formula directly so they may be changed at a later date.

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td><td style="background-color: rgb(192, 192, 192);">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">(Name)</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="3" style="background-color: rgb(192, 192, 192); text-align: right;">Usually being rotten:</td><td style="text-align: center;">25%</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rot</td><td>=' '!$H$2</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="3" style="background-color: rgb(192, 192, 192); text-align: right;">Special Deliveries:</td><td style="text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: center;">TruckLoad</td><td>=' '!$H$3</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="3" style="background-color: rgb(192, 192, 192); text-align: right;">Eaten by Ants:</td><td style="text-align: center;"> 2/5</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Ants</td><td>=' '!$H$4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="4" style="text-align: right;">=INDIRECT(ADDRESS(ROW(),2,3))</td><td style="background-color: rgb(192, 192, 192);">Amount</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="4" style="text-align: right;">=INDIRECT(ADDRESS(ROW(),3,3))</td><td style="background-color: rgb(192, 192, 192);">Delivered</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>[Late note: after the HTML code was issued and pasted, "Driver" was also defined and given the initial value of 10.]


Now, the formulas may change according the type of goods.
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 166px;"><col style="width: 117px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(255, 153, 204);">=Amount*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 255);">=(Amount/4)/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 0);">=Amount*Ants</td><td style="background-color: rgb(255, 153, 0);">=Amount*(1.3*Rot)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="background-color: rgb(153, 204, 255);">=Amount/4</td><td style="background-color: rgb(204, 255, 204);">=0</td></tr></tbody></table>

Note that the difference in the formulas doesn't even need to be precisely in 'Pairs'; While the yellow 'Pair' is common, it can combine a Yellow 'Rotten' formula with a different 'Delivered' one; and a purple 'Delivered' formula may be combined either with a Yellow formula or a Pink one.
Also note, that the Same 'Rotten' formula may take different results from the initial same amount: as the Yellow formula derives its data from the Delivery result, its numbers will be different for Oranges/Grapefruits than when applied for Watermelons/Apples. Also, on both cases of using the Purple formula (Bananas/Apples), the initial data for the Rotten formula will be different, as one will derive its calculations from the Delivered amount and the other from the Original amount.

Do not try to find any coherence in this scheme. There may be none. There may be others. The numbers may change. Formulas may be added.
Hence a solution which will involve a straight nested IF statement will prove itself to be both cumbersome and inefficient in the long run, and prone to much error on the first needed instance for manual updates.

(To complicate things even further we may change the Rot variable according to a lookup table which takes into account the time of the year. But aren't things complicated enough as they are?)

The straight numbers for the test case are as follows:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">244</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="text-align: right;">400</td><td style="text-align: right;">325</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="text-align: right;">250</td><td style="text-align: right;">0</td></tr></tbody></table>

Now, ideally, I would now regard this table as a 'Formula Template' table, and in the real table will point to the result in Delivered/Rotten columns for the correct calculation:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 269px;"><col style="width: 264px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>=INDEX(F.Del,MATCH($A12,$A$2:$A$8,0))</td><td>=INDEX(F.Rot,MATCH($A12,$A$2:$A$8,0))</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">500</td><td style="text-align: center;">(etc.)</td><td style="text-align: center;">(etc.)</td></tr></tbody></table>
(F.Del & F.Rot being Defined names for $C$2:$C$8 & $D$2:$D$8 ranges, respectively)

However, of course, the result takes its value directly from the result given at the 'Template formula, and does not change upon changes in the Amount column.
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">500</td><td style="text-align: right;">976</td><td style="text-align: right;">244</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">8</td><td style="text-align: right;">250</td><td style="text-align: right;">0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td>
</td><td style="text-align: right;">400</td><td style="text-align: right;">325</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr></tbody></table>
So, obviously, I need a way of pointing the C & D cells to the actual Formula rather than to a Formula Instance, or should we say an already-executed Formula.

I turned back to Names, thinking I can define a named Formula, then issue that. As I already use and have defined names for the Amount & Delivered cells (not ranges, but relative cells - note the first table example above), I could define the following:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 166px;"><col style="width: 107px;"><col style="width: 28px;"><col style="width: 117px;"><col style="width: 107px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Formula</td><td style="background-color: rgb(192, 192, 192); text-align: center;">its Defined Name</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td><td style="background-color: rgb(192, 192, 192); text-align: center;">its Defined Name</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">D.Normal</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">D.Normal</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(204, 153, 255);">D.Bananas</td><td>
</td><td style="background-color: rgb(255, 153, 204);">=Amount*Rot</td><td style="background-color: rgb(255, 153, 204);">R.Bananas</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 255);">=(Amount/Truckload)/(1+Rot)</td><td style="background-color: rgb(0, 255, 255);">D.Watermelons</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(204, 153, 255);">D.Apples</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 0);">=Amount*Ants</td><td style="background-color: rgb(0, 255, 0);">D.Kiwi</td><td>
</td><td style="background-color: rgb(255, 153, 0);">=Amount*(1.3*Rot)</td><td style="background-color: rgb(255, 153, 0);">R.Kiwi</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="background-color: rgb(153, 204, 255);">=Amount/TruckLoad</td><td style="background-color: rgb(153, 204, 255);">D.Bricks</td><td>
</td><td style="background-color: rgb(204, 255, 204);">=0</td><td style="background-color: rgb(204, 255, 204);">R.Bricks</td></tr></tbody></table>

And indeed, when taking my 'Template Table" and putting the appropriate Formula name directly, it works:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">244</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="text-align: right;">400</td><td style="text-align: right;">325</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="text-align: right;">250</td><td style="text-align: right;">0</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=D.Normal</td></tr><tr><td>D2</td><td>=R.Normal</td></tr><tr><td>C3</td><td>=D.Normal</td></tr><tr><td>D3</td><td>=R.Normal</td></tr><tr><td>C4</td><td>=D.Bananas</td></tr><tr><td>D4</td><td>=R.Bananas</td></tr><tr><td>C5</td><td>=D.Watermelons</td></tr><tr><td>D5</td><td>=R.Normal</td></tr><tr><td>C6</td><td>=D.Apples</td></tr><tr><td>D6</td><td>=R.Normal</td></tr><tr><td>C7</td><td>=D.Kiwi</td></tr><tr><td>D7</td><td>=R.Kiwi</td></tr><tr><td>C8</td><td>=D.Bricks</td></tr><tr><td>D8</td><td>=R.Bricks</td></tr></tbody></table></td></tr></tbody></table>

Now the next logical step, is to VLookup the Formula's Name in the above coloured table and match it against the appropriate Type. So, naming the above table FormulaLookup, I would -
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 220px;"><col style="width: 220px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>=Vlookup($A12,FormulaLookup,4,0)</td><td>=Vlookup($A12,FormulaLookup,7,0)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">500</td><td style="text-align: center;">(etc.)</td><td style="text-align: center;">(etc.)</td></tr></tbody></table>

And at the end of the road, my result is this:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>D.Normal</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td>D.Normal</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td>D.Bananas</td><td>R.Bananas</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>D.Normal</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">500</td><td>D.Apples</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td>D.Bananas</td><td>R.Bananas</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">8</td><td>D.Bricks</td><td>R.Bricks</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td>
</td><td>D.Kiwi</td><td>R.Kiwi</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td>D.Watermelons</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td>D.Watermelons</td><td>R.Normal</td></tr></tbody></table>

...which is exactly not what I intended.


Now, as it seems that by this point, all of the formula auto-choosing logics actually -work-, and that this design actually makes it possible to add or deduct more such formulas & formula schemes and to change the variables while affecting the should-be-issued formula, all that remains is causing the final stage to actually return the =D.Name value[?] instead of a "D.Name" text string. My current attempts at achieving this included both enclosing the VLookup function within a Value() function; adding "="&Vlookup(etc), both in-and-out of Value() - and so far, all my attempts were unsuccessful.


Any insights will be appreciated.


-- Lorem Ipsum

 

[jasonb75]

There are politicians that make more sense than your last post

Rather than me trying to make unsuccessful attempts at figuring out your multicoloured pairs table, or how the sum of rotten kiwi is equal to PI n' apple / pomgrenade, can you work with the following?

Table 1 should simply identify which "Type" each fruit is classified as.

Table 2 should contain the calculation formula for each "Type"

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 89px"><COL style="WIDTH: 44px"><COL style="WIDTH: 64px"><COL style="WIDTH: 77px"><COL style="WIDTH: 84px"><COL style="WIDTH: 85px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Table 1</TD><TD></TD><TD></TD><TD>Table 2</TD><TD>Result 1</TD><TD>Result 2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Oranges</TD><TD>Type1</TD><TD></TD><TD>Type1</TD><TD>a/1.25</TD><TD>a-b</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Grapefruits</TD><TD>Type2</TD><TD></TD><TD>Type2</TD><TD>a/1.25/4</TD><TD>b*0.25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Bananas</TD><TD>Type3</TD><TD></TD><TD>Type3</TD><TD>a/4</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>watermelons</TD><TD>Type4</TD><TD></TD><TD>Type4</TD><TD>(a-200)/1.25</TD><TD>(a/1.25)*2/3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>apples</TD><TD>Type2</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
Note that in table 2, the formula is not preceded by =, in addition references to the "Amount" are substituted with "a" and in result 2, references to result 1 are substituted with "b"

Both tables must be named (table1, table2) for the code to recognise them.

This, accompanied by the following code in the worksheet module will insert the correct formula where required.

Code is based on fruit in column A, Amount in column B (fruit muist be entered before amount to avoid errors). Results will then be posted to columns C & D

Sub worksheet_change(ByVal target As Range)
If target.Column <> 2 Then Exit Sub
On Error GoTo getout
type1 = WorksheetFunction.VLookup(target.Offset(, -1), ActiveSheet.Range("Table1"), 2, 0)
result1 = WorksheetFunction.VLookup(type1, ActiveSheet.Range("Table2"), 2, 0)
result2 = WorksheetFunction.VLookup(type1, ActiveSheet.Range("Table2"), 3, 0)
If InStr(result1, "a") Then
   result1 = Replace(result1, "a", target.Value)
End If
target.Offset(, 1) = "=" & result1
If InStr(result2, "a") Then
   result2 = Replace(result2, "a", target.Value)
End If
If InStr(result2, "b") Then
   result2 = Replace(result2, "b", target.Offset(0, 1).Value)
End If
target.Offset(, 2) = "=" & result2
Exit Sub
getout:
MsgBox target.Offset(, -1) & " Not found in lookup table."
End Sub