Formula dependent on cell

**asdvender** · 12-22-2008, 12:12 AM

Hello all,

I am back with yet another question.
This time I am trying to get a cell to obtain a formula depending on another cell...here's the example and my attempt.

Cell A1... ="FA"

Please Login or Register  to view this content.

I want A2 to generate a formula based on the number or characters in cell A1, since this example results in 2 characters, I want to lookup the formula in the table associated to 2. However, this example just returns the value of the cell instead of the formula in the table. Is there a way to return a formula instead? I am manually updating the formulas everytime I need now, but would be tons easier if it was automated.

Thanks!
I can post a example spreadsheet if necessary.

**shg** · 12-22-2008, 12:55 AM

I can post a example spreadsheet if necessary.

Please do, and explain what you want in context.

**asdvender** · 12-23-2008, 09:07 PM

Okay, it may be a bit confusing...
Instead of having to manually update the formulas in L2:U26, depending on the number of characters in L1:U1, I would like it to automatically reference L36:M39 and use the appropriate formula...

My attempt in column S:

Please Login or Register  to view this content.

I used the evaluate formula feature and noticed the formula stopped at the end of the reference...as in, it will copy the value in the reference cell, but not the formula...

Any ideas to have it copy the formula?

Thanks!

**asdvender** · 12-24-2008, 03:06 PM

Here is what the Evaluate Formula feature returns...

Please Login or Register  to view this content.

**shg** · 12-24-2008, 03:15 PM

The formula in S2 works fine; it's returning the result #VALUE!, which is exactly what's in the second columns of the lookup table L36:M39.

**asdvender** · 12-25-2008, 11:27 AM

I noticed an error in my last sheet so I am uploading a new one...nothing much changed...my reference sheet was one column off...

Originally Posted by shg

The formula in S2 works fine; it's returning the result #VALUE!, which is exactly what's in the second columns of the lookup table L36:M39.

That is correct, but that is the second column of the reference table's VALUE which was COPIED, I need the FORMULA to be COMPUTED.

Since T1 is "1245", there are 4 characters. Therefore I would like cells T2:T26 to compute formula M38. If you copy-paste cell M38 into T2:T26 manually, the cells will actually compute the formula, not return the "#VALUE!" result from my attempt.

My attempt, however returns the VALUE of the reference from the table, I would like it reference the formula.

The point of L:U is to identify which cells agree from G:K, depending on previous results. So L1:U1 will identify which cells are worth taking a look at. For example, L column is "13", so if G2:I2 (first and third columns) are both v or n, it will put a 1 in column L2, G3:I3 disagree therefore it is blank, and etc...is there an easier way of doing this?

Thanks!

**shg** · 12-25-2008, 02:01 PM

I can't get my brain around what M36:M39 are trying to compute.

**asdvender** · 12-25-2008, 08:05 PM

Originally Posted by shg

I can't get my brain around what M36:M39 are trying to compute.

I assigned Columns G:K numbers...
G=1
H=2
I=3
J=4
K=5

M1 says "14"...meaning that when column 1=4 (column G=J) are the same it is correct 4 times and wrong 6 times...
M2:M26 returns a value of 1 when they agree and returns nothing when they do not.

Breaking down the formula...
=IF(INDIRECT(VLOOKUP(VALUE(LEFT(M$1,1)),ref!$D$2:$E$6,2,0))<>"",IF((INDIRECT(VLOOKUP(VALUE(LEFT(M$1,1)),ref!$D$2:$E$6,2,0))=INDIRECT(VLOOKUP(VALUE(RIGHT(M$1,1)),ref!$D$2:$E$6,2,0))),1,""),"")

The first part of the formula says if M1 is blank, output nothing...otherwise check if column 1=4. If so, return a value of 1, otherwise, return nothing. The reference is to check what column a value of 1 refers to, and same thing for column 4.

Does that help?