Conditional format or what else

[willemeulen]

I have the following chalange.

I have created a sheet thus far working perfectly and now I want to put
finishing touch. Column A(type Code) and H(shape code) return a certain
formula from an index page. In turn the formula will use user defined values
(lengts for sections of the shape), depending on the shape code formula uses
between between 1 and 5 values to calculate the total length of the item.

Is there a method (conditional formating or other) to highlight/mark or
other techniques to let cells stand out which are used in the formula. This
would make it easy to spot any shortfalls when information is left out. An
alternative route could be shape code, the shape code already tells me how
many values are needed to calculate total length.


Thanks,

Willem

 

[willemeulen]

That is not my chalange simon,

The user will need to fill in the type code and shape code, thereafter the
spreadsheet automatically determines the formula (index/lookup function).
Depending on the formula the user must fill out valuea A,B,C,D etc. I want
exell to automatically mark/shade the cells which need a value.

The shading or marking will actually do nothing to the formula itself but
the user can see in a quick scan if all the necessary information is there.
It is easy to forget a value.

Example
shape code 31 - U shape and formula uses 3 values to calculate length (A,B,C)
Shape code 1 - Straight bar, formula only needs one value (A)
Shape code 5 - L shape, formula needs 2 values (A & B)
Shape codes can have up to 5 values depending on the variaty of possibilities

The above example is simlified. The formula is determined by indexing shape
code and type code. Type code refers to the type of material/diameter which
influences the bending radiusus. The bending radius reduces the total length
only slightly and will cause a certain reduction. The final formula which is
returned gives the cut length needed to bend that shape with a certain
diameter/material. As the example shows every shape has its on values
(A,B,C...E). These values must be inserted by the user. My goal is that the
user can now easily see which values he needs to insert, and I can double
check the sheet quickly and easily.

I could think of 2 different approaches,

1 excell can see which cells are needed by the formula and gives them a
shading
2 I could have another table which gives me the amount of cells/value's
which need to be inserted, and excell shades the next 1,2,3,4 5 cells