I have a spreadsheet with a number of characteristics listed (column E), each with a set level at which they become available (with the level shown in column B) and a points cost (column G). Some require other characteristics as prerequisites (column I), others are duplicated elsewhere on the sheet (for instance, the characteristics in E208 and E251), and others may be selected several times (times available listed in column F, with the number of times selected in column J). There's a drop-down list in cell E2 listing (levels) 1-8. Characteristics are selected by entering a "1" (or, for multiply-selectable characteristics, another number) in column J.
Ideally, when I refer to formatting (greying out etc) I'd like it to only apply to the parts within the table (columns B-K) rather than the whole row. But that's just wallpaper.
What I would like it to do is fourfold:
Ideally, when I refer to formatting (greying out etc) I'd like it to only apply to the parts within the table (columns B-K) rather than the whole row. But that's just wallpaper.
What I would like it to do is fourfold:
- Hide all rows with levels above the level selected in E2 (for instance, "Logic" in E67 is level 2 (C67 = "2"); if E2=1, it should be hidden, and if E2>=2 it should be visible).
- Grey out the background in rows that don't meet the prerequisites (for instance, grey out row 121 if the sum of cells J85, J208 and J251 (the prerequisite characteristic, in this case available in three places) = 0)
- With duplicates, grey out the background in all but the cheapest; in that example, the costs in G85 (400), G208 (400) and G251 (200) should be compared, and rows 85 and 208 greyed out. If the one in row 251 wasn't available until level 4, for instance, the other two should remain normal until level 4 is selected to be made visible.
- Grey out the text of rows where the cost in column G is greater than the balance remaining (listed in K1).
