Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement result
I have a formula that is currently in my Excel spreadsheet, but we want to
just show the result in the spreadsheet and not the formula. We also have too many nested functions for one cell. Can I accomplish both throught VBA? Here is my formula with too many functions: =IF($S9=2,IF(ISERROR(INDEX(TABLE,MATCH($B9,blue,0) ,MATCH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MA TCH($B9,brown,0),MATCH($T$8,HEADERS,0))),IF(ISERRO R(INDEX(TABLE,MATCH($B9,green,0),MATCH($T$8,HEADER S,0))),IF(ISERROR(INDEX(TABLE,MATCH($B9,red,0),MAT CH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MATCH( $B9,yellow,0),MATCH($T$8,HEADERS,0))),"Research",I NDEX(TABLE,MATCH($B9,yellow,0),MATCH($T$8,HEADERS, 0))),INDEX(TABLE,MATCH($B9,red,0),MATCH($T$8,HEADE RS,0))),INDEX(TABLE,MATCH($B9,green,0),MATCH($T$8, HEADERS,0))),INDEX(TABLE,MATCH($B9,brown,0),MATCH( $T$8,HEADERS,0))),INDEX(TABLE,MATCH($B9,blue,0),MA TCH($T$8,HEADERS,0))),"") this formula basically finds a value in column B to various columns on different sheets (the colors) and returns the value in the same row under the heading in T9. It worked until I put yellow in. But, if I can put just the result of the formula in the spread sheet would not take so long to download the table. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement result
Try this
call with =GetValue($S9, $B9, $T$8) Function GetValue(NumberValue, RowItem, ColumnItem) If NumberValue = 2 Then ColNumber = Range("Headers").Find(what:=ColumnItem, _ LookIn:=xlValues, lookat:=xlWhole) If ColNumber Is Nothing Then GetValue = "Cannot find : " & MatchColumn Exit Function End If RowNumber = Range("blue").Find(what:=RowItem, _ LookIn:=xlValues, lookat:=xlWhole) If RowNumber Is Nothing Then RowNumber = Range("brown").Find(what:=RowItem, _ LookIn:=xlValues, lookat:=xlWhole) If RowNumber Is Nothing Then RowNumber = Range("green").Find(what:=RowItem, _ LookIn:=xlValues, lookat:=xlWhole) If RowNumber Is Nothing Then RowNumber = Range("red").Find(what:=RowItem, _ LookIn:=xlValues, lookat:=xlWhole) If RowNumber Is Nothing Then GetValue = "research" Exit Function End If End If End If End If GetValue = Range("Table")(RowNumber, ColNumber) Else GetValue = "" End If End Function "iashorty" wrote: I have a formula that is currently in my Excel spreadsheet, but we want to just show the result in the spreadsheet and not the formula. We also have too many nested functions for one cell. Can I accomplish both throught VBA? Here is my formula with too many functions: =IF($S9=2,IF(ISERROR(INDEX(TABLE,MATCH($B9,blue,0) ,MATCH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MA TCH($B9,brown,0),MATCH($T$8,HEADERS,0))),IF(ISERRO R(INDEX(TABLE,MATCH($B9,green,0),MATCH($T$8,HEADER S,0))),IF(ISERROR(INDEX(TABLE,MATCH($B9,red,0),MAT CH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MATCH( $B9,yellow,0),MATCH($T$8,HEADERS,0))),"Research",I NDEX(TABLE,MATCH($B9,yellow,0),MATCH($T$8,HEADERS, 0))),INDEX(TABLE,MATCH($B9,red,0),MATCH($T$8,HEADE RS,0))),INDEX(TABLE,MATCH($B9,green,0),MATCH($T$8, HEADERS,0))),INDEX(TABLE,MATCH($B9,brown,0),MATCH( $T$8,HEADERS,0))),INDEX(TABLE,MATCH($B9,blue,0),MA TCH($T$8,HEADERS,0))),"") this formula basically finds a value in column B to various columns on different sheets (the colors) and returns the value in the same row under the heading in T9. It worked until I put yellow in. But, if I can put just the result of the formula in the spread sheet would not take so long to download the table. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you highlight the result of an IF statement | Excel Worksheet Functions | |||
How to create an if statement to get a multiple result | Excel Discussion (Misc queries) | |||
Unexpected result from IF statement... Help with formula. | Excel Programming | |||
IF statement result | Excel Worksheet Functions | |||
is statement with an empty cell as result | Excel Worksheet Functions |