Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions in programming
Does anyone know how many functions excel can handle in a single cell? I
have a pretty long formula that searches three tables for values and need to add three more variables that will search three separate tables. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions in programming
Do you have an example of what you are trying to accomplish?
-- John C "ashoulds" wrote: Does anyone know how many functions excel can handle in a single cell? I have a pretty long formula that searches three tables for values and need to add three more variables that will search three separate tables. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions in programming
Here is a copy of the code.I need to add code that will use values for b6= 2,
3, 4 and the arrays for the tables in the vlookupfunction will change as well. Just wondering if it would actually work. =(IF(AND(J8=1,B6=1),IF(ISNA(VLOOKUP(E19,'Calculati ons 2'!B3:C64,2,0)),INDEX('Calculations 2'!B3:C64,MATCH(Calculations!E19,'Calculations 2'!B3:B64,1)+1,2),VLOOKUP(E19,'Calculations 2'!B2:C64,2,0)),IF(AND(J8=2,B6=1),IF(ISNA(VLOOKUP( E19,'Calculations 2'!I3:J64,2,0)),INDEX('Calculations 2'!I3:J64,MATCH(Calculations!E19,'Calculations 2'!I3:I64,1)+1,2),VLOOKUP(E19,'Calculations 2'!I3:J64,2,FALSE)),IF(AND(J8=3,B6=1),IF(ISNA(VLOO KUP(E19,'Calculations 2'!Q3:R64,2,0)),INDEX('Calculations 2'!Q3:R64,MATCH(Calculations!E19,'Calculations 2'!Q3:Q64,1)+1,2),VLOOKUP(E19,'Calculations 2'!Q3:R64,2,0)))))) I need to add code that will use values for b6= 2, 3, 4 and the arrays for the tables in the vlookupfunction will change as well. Just wondering i "John C" wrote: Do you have an example of what you are trying to accomplish? -- John C "ashoulds" wrote: Does anyone know how many functions excel can handle in a single cell? I have a pretty long formula that searches three tables for values and need to add three more variables that will search three separate tables. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions in programming
May I make a recommendation, first, create a table elsewhere, that will
determine the 'table' that you will be utilizing (B3:C64, I3:J64, Q3:R64, etc..) Also, I noticed 2 discrepancies in your formula as given. All your table references are in the above mentioned format, except you have one reference to B2:C64, and another reference to Q3:Q64; I am assuming these are errors. I created another worksheet, called Tables, and in A1:B10, I typed the following: 11 B3:C64 21 I3:J64 31 Q3:R64 etc..., down as far as needed. Also assuming that J8 & B6 are single digits, the values 11, 21, 31, are essentially your J8 & B6 values just combined. Once that is done, you should be able to do a formula like this, note this was tested only partially, but I think you can see where I am going with it: =IF(COUNTIF(Tables!$A$1:$A$10,--(J8&B6))=0,"",IF(ISNA(VLOOKUP(E19,INDIRECT("'Calcu lations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),2,0)),INDEX(IN DIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),MATCH(Calculat ions!E19,INDIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),1)+1,2),VLOOKU P(E19,INDIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),2,0))) You can obviously adjust the reference to Tables!$A$1:$B$10 as needed depending on where you setup the table at. Hope this helps. -- John C "ashoulds" wrote: Here is a copy of the code.I need to add code that will use values for b6= 2, 3, 4 and the arrays for the tables in the vlookupfunction will change as well. Just wondering if it would actually work. =(IF(AND(J8=1,B6=1),IF(ISNA(VLOOKUP(E19,'Calculati ons 2'!B3:C64,2,0)),INDEX('Calculations 2'!B3:C64,MATCH(Calculations!E19,'Calculations 2'!B3:B64,1)+1,2),VLOOKUP(E19,'Calculations 2'!B2:C64,2,0)),IF(AND(J8=2,B6=1),IF(ISNA(VLOOKUP( E19,'Calculations 2'!I3:J64,2,0)),INDEX('Calculations 2'!I3:J64,MATCH(Calculations!E19,'Calculations 2'!I3:I64,1)+1,2),VLOOKUP(E19,'Calculations 2'!I3:J64,2,FALSE)),IF(AND(J8=3,B6=1),IF(ISNA(VLOO KUP(E19,'Calculations 2'!Q3:R64,2,0)),INDEX('Calculations 2'!Q3:R64,MATCH(Calculations!E19,'Calculations 2'!Q3:Q64,1)+1,2),VLOOKUP(E19,'Calculations 2'!Q3:R64,2,0)))))) I need to add code that will use values for b6= 2, 3, 4 and the arrays for the tables in the vlookupfunction will change as well. Just wondering i "John C" wrote: Do you have an example of what you are trying to accomplish? -- John C "ashoulds" wrote: Does anyone know how many functions excel can handle in a single cell? I have a pretty long formula that searches three tables for values and need to add three more variables that will search three separate tables. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions in programming
On Wed, 8 Oct 2008 06:33:01 -0700, ashoulds
wrote: Does anyone know how many functions excel can handle in a single cell? I have a pretty long formula that searches three tables for values and need to add three more variables that will search three separate tables. Check HELP for your Excel under Specifications. There are nesting limits (different for different versions), and limits on the length (number of characters) of a formula. I don't believe there are limits as to the number of functions. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba programming | Excel Discussion (Misc queries) | |||
programming help | Excel Discussion (Misc queries) | |||
programming | Excel Discussion (Misc queries) | |||
Programming help | Excel Discussion (Misc queries) | |||
programming date functions | Excel Worksheet Functions |