Use index function to return formula

[willemeulen]

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

 

[Bernard Liengme]

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

 

[Martin Fishlock]

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)

 

[Roger Govier]

Hi Willem

An alternative, non-volatile formula would be
=INDEX(P,ROW())+INDEX(Q:Q,ROW())-(INDEX(R:R,ROW())*8)