PDA

View Full Version : Map Number Ranges to other Sheets?



binar
11-15-2011, 02:31 PM
Fellow Forum Members,
Not sure if this can be done with a formula or if it requires a VBA script.

Phase 1 involves entering the following source data:
In Sheet1 cell A1 I enter the starting counting number of "1". In cell B1 I enter the ending counting number of "10".
In Sheet1 cell A2 I enter the starting counting number of "11". In cell B2 I enter the ending counting number of "36".
In Sheet1 cell A3 I enter the starting counting number of "37". In cell B3 I enter the ending counting number of "51

Phase 2 Excel now needs to do the following magic:
In Sheet2, cells A1 thru A10 the numbers "1" thru "10" automatically appear.
In Sheet3, cells A1 thru A26 the numbers "11" thru "36" automatically appear.
In Sheet4, cells A1 thru A15 the numbers "37" thru "51" automatically appear.

In short, I'm entering X amount of ranges of numbers in Sheet1 and I need these number ranges mapped out to Sheets 2, 3, 4, etc... as ordinal numbers. Since I have 12 different number ranges to map to 12 different worksheets, I'm thinking the easiest way would be to have a VBA that I could assign to a unique Command Button and customize each script to point to the correct Sheet# and populate with correct number sequence. Any help will be greatly appreciated. Thanks. : pray2:

mdmackillop
11-15-2011, 04:01 PM
Sheet2 A1 =IF(ROW()<=Sheet1!B$1,ROW(),"") & copy down
Sheet3 A1 =IF(ROW()<=Sheet1!A$3-Sheet1!A$2,ROW()+Sheet1!A$2-1,"") + copy down
Sheet4 A1 =IF(ROW()<=Sheet1!A$4-Sheet1!A$3,ROW()+Sheet1!A$3-1,"") + copy down
and so on

or
Option Explicit
Sub Mapping()
Dim Rng As Range, i As Long, j As Long, k As Long
Set Rng = Sheets(1).Range("A1").CurrentRegion
For i = 1 To Rng.Rows.Count
k = 0
For j = Rng(i, 1) To Rng(i, 2)
k = k + 1
Sheets(i + 1).Cells(k, 1) = j
Next
Next
End Sub