Format a cell based on incremental numbers

[MurrayBarn]

I have a workbook with multiple sheets. On one sheet, I have a cell where I
manually change the contents from 1 to 12 (corresponding to months of the
year). This number then is used in formulas to sum columns 1-12, depending on
what the number is I have entered.

Is there a way to format a cell, say fill the cell with a colour, above each
column that is being summed as described above so that the columns that are
not being summed have a blank cell above them?

 

[Bernard Liengme]

I think you could use conditional formatting but I did not quite follow what
you meant by
(1) "This number then is used in formulas to sum columns 1-12, depending on
what the number is I have entered"
Suppose it is A1 that has the magic number, and its value is 6; then you add
cells B1 to B6 (or something like this)?
or
(2) "Say fill the cell with a colour, above each column that is being summed
as described above so that the columns that are not being summed have a
blank cell above them?"
What is in the cells that are to become blank? Conditional formatting could
change the font colour to make them invisible but it cannot remove values.
best wishes

 

[MurrayBarn]

Hi Bernard

I will try and simplify my explanation as follows:

Lets say that in cell A1 I type a number from 1 to 12 which would correspond
to a month, so if I type a 6, it would correspond to June for instance. This
would be on a sheet called DataEntry.

Now, on another sheet (called Data), I have columns A - L for January,
February .... to December. In Column M I have a formula that sums the row the
formula is in from A-L, BUT it is based on the number in A1 referred to
above. So in this case, the formula in M will sum the first 6 columns. So
lets say in row 2, I have Sales, by typing 6 into A1 on the sheet DataEntry,
the formula in M2 on Data will sum A2 to F2 on Data.

Because cell A1 with the 6 in it is on a different sheet, I cant quickly
tell which columns are being summed on Data. What I would like is a simple
formula or conditional formatting that will cause the cells in Row A on the
sheet Data to change colour if that column is included in the formula in
column M. In other words, by typing 6 in A1 on DataEntry, the cells A1 to F1
on Data turn yellow, so I can see at a glance what data is included in the
formula M.

Hope you understand AND can help.

Regards

 

[MurrayBarn]

I have managed to work out a solution using IF statements and conditional
formatting. Not v elegant, but it works