Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use index function to return formula
In my spreadsheet I have two values which lead to a certain formula, the type
code and shape code to be specific. Like I said these values lead to a formula, the formula itself refers to a set of cells in the same row with values which are different per situation and need to be completed by the user. Firstly how do I return a formula refering to column number (row number is dependent on the row you are in. Example for row 20 type code 8 shape code 15 Formula =P?? + Q?? - R?? * 8 (because we are in row 20 it becomes P20+Q20-R20*8) Secondly: Depending on the shape code (between 1-99) it determines how manny additional values are needed for the formula, or formula itself Can excel automatically shade the cells which need values? For example Shape code 4 needs value A,B and C Shape code 3 only needs value A Shape code 92 needs value A,B,C,D and E Thanks, Willem |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use index function to return formula
First part:
=INDIRECT("P"&ROW())+INDIRECT("Q"&ROW())-INDIRECT("R"&ROW())*8 Second part: please look in Help under 'Conditional Formatting' then come back with more questions. Tell us what cell will hold the value of 'shape type' best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "willemeulen" wrote in message ... In my spreadsheet I have two values which lead to a certain formula, the type code and shape code to be specific. Like I said these values lead to a formula, the formula itself refers to a set of cells in the same row with values which are different per situation and need to be completed by the user. Firstly how do I return a formula refering to column number (row number is dependent on the row you are in. Example for row 20 type code 8 shape code 15 Formula =P?? + Q?? - R?? * 8 (because we are in row 20 it becomes P20+Q20-R20*8) Secondly: Depending on the shape code (between 1-99) it determines how manny additional values are needed for the formula, or formula itself Can excel automatically shade the cells which need values? For example Shape code 4 needs value A,B and C Shape code 3 only needs value A Shape code 92 needs value A,B,C,D and E Thanks, Willem |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use index function to return formula
Hi:
You can use the indirect or offset functions. The indirect is =INDIRECT("P" & ROW()) The offset is =OFFSET(A1, ROW()-1, 15) or if you do not know the number try =OFFSET(A1, ROW()-1, column("P1")-1) -- Hope this helps Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK Please do not forget to rate this reply. "willemeulen" wrote: In my spreadsheet I have two values which lead to a certain formula, the type code and shape code to be specific. Like I said these values lead to a formula, the formula itself refers to a set of cells in the same row with values which are different per situation and need to be completed by the user. Firstly how do I return a formula refering to column number (row number is dependent on the row you are in. Example for row 20 type code 8 shape code 15 Formula =P?? + Q?? - R?? * 8 (because we are in row 20 it becomes P20+Q20-R20*8) Secondly: Depending on the shape code (between 1-99) it determines how manny additional values are needed for the formula, or formula itself Can excel automatically shade the cells which need values? For example Shape code 4 needs value A,B and C Shape code 3 only needs value A Shape code 92 needs value A,B,C,D and E Thanks, Willem |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use index function to return formula
Hi Willem
An alternative, non-volatile formula would be =INDEX(P:P,ROW())+INDEX(Q:Q,ROW())-(INDEX(R:R,ROW())*8) -- Regards Roger Govier "willemeulen" wrote in message ... In my spreadsheet I have two values which lead to a certain formula, the type code and shape code to be specific. Like I said these values lead to a formula, the formula itself refers to a set of cells in the same row with values which are different per situation and need to be completed by the user. Firstly how do I return a formula refering to column number (row number is dependent on the row you are in. Example for row 20 type code 8 shape code 15 Formula =P?? + Q?? - R?? * 8 (because we are in row 20 it becomes P20+Q20-R20*8) Secondly: Depending on the shape code (between 1-99) it determines how manny additional values are needed for the formula, or formula itself Can excel automatically shade the cells which need values? For example Shape code 4 needs value A,B and C Shape code 3 only needs value A Shape code 92 needs value A,B,C,D and E Thanks, Willem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula on INDEX function not working | Excel Worksheet Functions | |||
Using INDEX function to return array row. | Excel Worksheet Functions | |||
INDEX function need to have col reference to be formula | Excel Worksheet Functions | |||
Index? Match? Function to sort and return value fr diff column in | Excel Worksheet Functions | |||
Error Return Value from and INDEX(A:2,MATCH()) function | Excel Worksheet Functions |