Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format a cell based on incremental numbers
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format a cell based on incremental numbers
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MurrayBarn" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format a cell based on incremental numbers
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MurrayBarn" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format a cell based on incremental numbers
I have managed to work out a solution using IF statements and conditional
formatting. Not v elegant, but it works "MurrayBarn" wrote: 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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MurrayBarn" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
incremental counting based on criteria | Excel Discussion (Misc queries) | |||
Autofilling incremental numbers in a formula | Excel Worksheet Functions | |||
how do i format a cell based on format of a range of cells? | Excel Discussion (Misc queries) | |||
chart based on incremental difference | Charts and Charting in Excel | |||
How do I create incremental numbers on an order form? | Excel Discussion (Misc queries) |