In Over My Head

[JSnow]

Hello again Gods of Excel. Here's what I'm struggling with this week: I need
to use the value from sheet "SET UP" cell "C2" (which is a whole number,
let's say 50) on all my other sheets (Jan - Dec) in as a counter. I'd like
to accomplish two things on all the other sheets: 1) starting w/ row 6 in
column A, list 1 through whatever the number for C2 is on "SET UP" sheet; 2)
format cells B6:K56 (row 6 + 50 in this example) with white background, grey
shading etc.

So if I entered 50 on the SET UP sheet, all the other pages would have 50
lines formatted with 1-50 listed in column A. What if I changed that number
to 30? Wouldn't the formatting remain for the rows labelled 31-50?

Thanks in advance for any help.

 

[Don Guillett]

One way assuming Setup is your first sheet.
If you do NOT want to clear the old numbers delete the .columns(1) line

Sub numbersinshts()
mynum = 8 'cells("c2")
For i = 2 To Sheets.Count
With Sheets(i)
.Columns(1).ClearContents
With .Cells(6, 1).Resize(mynum)
.Formula = "=Row(a1)"
.Value = .Value
End With
End With
Next i
End Sub

 

[MyVeryOwnSelf]

Hello again Gods of Excel. Here's what I'm struggling with this week:

I need to use the value from sheet "SET UP" cell "C2" (which is a
whole number, let's say 50) on all my other sheets (Jan - Dec) in as a
counter. I'd like to accomplish two things on all the other sheets:
1) starting w/ row 6 in column A, list 1 through whatever the number
for C2 is on "SET UP" sheet; 2) format cells B6:K56 (row 6 + 50 in
this example) with white background, grey shading etc.

So if I entered 50 on the SET UP sheet, all the other pages would have
50 lines formatted with 1-50 listed in column A. What if I changed
that number to 30? Wouldn't the formatting remain for the rows
labelled 31-50?

I have Excel 2003. Maybe the following will help.

First, tackle the background/shading using conditional formatting. To do
this, start with
Insert > Name > Define
and
- for "Names in workbook" put "RowCount"
- for "Refers to" click on C2 on "SET UP" sheet

Then (a) select all the month-sheet tabs and (b) select the range of cells
to be formatted, and (c) use
Format > Conditional Formatting > Formula Is
with use the formula
=AND(ROW()>=6,ROW()<6+RowCount)
and click the format button to choose the desired background/shading.


Next comes the list starting at A6 of the "SET UP" sheet. In A6 put
=IF(ROW()-5>RowCount,"",
INDIRECT(TEXT(DATE(2008,COLUMN(),1),"Mmm")&"!$A"&ROW()))
Extend this to the right to L6. Select A6:L6 and extend down for the
maximum number of rows of data.