Help is needed with Conditional Formatting drudgery

K

K. Georgiadis

I am trying desperately to reduce mouse clicks (and
carpal tunnel syndrome risk!) with the following problem:

I have 90 SETS of three price calculations, each trio
equipped with its own drop down list which determines
whether certain cells should be blacked out. The
calculations are distributed among 30 tabs, an average of
3 sets of calculations per each tab.

I have been copying the blank forms over the different
tabs and I have been manually adjusting (a) the cell
links for each drop down list and (b) all the conditional
format formulas (I have been using the format painter to
as much as possible to copy over the conditional formats).

Are there any other shortcuts to reduce this drudgery?

Thank you for your help
 
D

Debra Dalgleish

If you copy cells with data validation and conditional formatting to a
different sheet, they should automatically refer to the same cells on
the new sheet. As long as your worksheets are consistently structured
you shouldn't have to adjust the links.
 
D

David McRitchie

Now that I probably understand the question better
thanks to Debra, sounds like the tables are changing
on you, which is a lot simpler than what my thinking you
wanted to eliminate parts of a table in each invocation.

Use absolute cell addresses for the actual tables,
so that the tables don't get adjusted, or else use named ranges.
The named range will apply to one range addressable from
all of the sheets in the workbook (at least normally that is).

named ranges
http://www.mvps.org/dmcritchie/excel/names.htm =

If there is additional cell interaction then perhaps you want to use
AND(condition1, ..., conditionN) or OR(cond1, ..., condN)
 
K

K. Georgiadis

I didn't explain this very well: let's say that the first
worksheet contains the drop down list (let's call it a
switch for convenience) with link cell $D$3, calculations
with conditional formatting, data validation, etc..

There is no problem when I copy the block of data to the
next worksheet, as long as it only needs to contain a
single block of calculations. However, some worksheets
need to contain as many as 9 sets of calculations, with
their own, INDEPENDENT switches. For calculations 2 to 9,
I have been manually changing the link cell address
because $D$3 is no longer correct. Similarly, the
conditional formatting formulas need to be amended to
refer to the new cell link.

I experimented with mixed and relative cell references,
but nothing has eliminated the need for laborious manual
changes.
 
Top