Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format or what else
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format or what else
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! willemeulen;332243 Wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92857 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format or what else
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" wrote: 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! willemeulen;332243 Wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92857 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format or what else
willemeulen;332374 Wrote: 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" wrote: 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! willemeulen;332243 Wrote: 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' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Conditional format or what else - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92857 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy conditional format without using format painter | Excel Worksheet Functions | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |