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

 

[Simon Lloyd]

For finding out what is attached to what in a formula use TOOLS form
your menubar then FORMULA AUDITING, then you can use trace dependants
etc. and a whole host of things to get your formulae right!

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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Simon Lloyd]

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


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
Forums' (http://www.thecodecage.com/forumz/showthread.php?t=92857)

I really can't get a grasp of what you mean, but you can use code like
this in the worksheet code module to change a colour of one cell
depending on the value in another cell and its not limited to 3
conditions!


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
Range("A6").Interior.ColorIndex = 3
Case Is = 2
Range("A6").Interior.ColorIndex = 4
Case Is = 3
Range("A6").Interior.ColorIndex = 5
Case Else
Range("A6").Interior.ColorIndex = xlNone
End Select
End Sub
-------------------



*How to get further help with a workbook*
For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will have the
opportunity to add attachmnets to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)