Automatically Enter Data
 

Hello,

In sheet2 of an excel I would to have 3 Columns. Name, Start row, End Row.

I would for example plug in

Derek, 2, 400
Bob, 401, 700
Jane,701,1000

Where my comma's denote the next column.

I would then like Derek entered in column A row 2, 3......400 (in sheet1).
Then I want Bob to be entered in column A, row 401,402...700. You get the
point. Is this possible?

Thanks in advance,
Derek

Oh yea, and I have way more than 7 different names with their respective row
ranges, so I don't think something using an IF function and a BETWEEN
function in sheet1!columnA would work well.....but I'll leave it to you
experts.

Thanks

Derek

one way:

Dim aParameters
Dim sName As String
Dim iStartRow As Integer
Dim iEndRow As Integer
Dim c As Range

'If LCase(ActiveSheet.Name) < "sheet2" Then Exit Sub ' or
If LCase(ActiveSheet.Name) < "sheet2" Then Sheets("Sheet2").Select
With Sheets("Sheet2")
For Each c In .Range(Range("A1"), .Range("A1").End(xlDown))
aParameters = Split(c.Value, ",")
sName = aParameters(0)
iStartRow = aParameters(1)
iEndRow = aParameters(2)
With Sheets("Sheet1")
.Range(.Cells(iStartRow, 1), .Cells(iEndRow, 1)) = sName
End With
Next 'c
End With

Regards

Trevor


"Derek Y via OfficeKB.com" wrote in message
...

Hello,

In sheet2 of an excel I would to have 3 Columns. Name, Start row, End
Row.

I would for example plug in

Derek, 2, 400
Bob, 401, 700
Jane,701,1000

Where my comma's denote the next column.

I would then like Derek entered in column A row 2, 3......400 (in sheet1).
Then I want Bob to be entered in column A, row 401,402...700. You get the
point. Is this possible?

Thanks in advance,
Derek

On Sheet 1, in cell A2, enter the formula:

=INDEX(Sheet2!$A$1:$A$20,MATCH(ROW(),Sheet2!$B$1:$ B$20,1))

Copy down to row 1000

Derek Y via OfficeKB.com wrote:
Hello,

In sheet2 of an excel I would to have 3 Columns. Name, Start row, End Row.

I would for example plug in

Derek, 2, 400
Bob, 401, 700
Jane,701,1000

Where my comma's denote the next column.

I would then like Derek entered in column A row 2, 3......400 (in sheet1).
Then I want Bob to be entered in column A, row 401,402...700. You get the
point. Is this possible?

Thanks in advance,
Derek


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Apologies

missed the "three columns" part

Same code, modified to take this into account (split not needed)

If LCase(ActiveSheet.Name) < "sheets2" Then Sheets("Sheet2").Select
With Sheets("sheet2")
For Each c In .Range(Range("A1"), .Range("A1").End(xlDown))
sName = c.Value
iStartRow = c.Offset(0, 1).Value
iEndRow = c.Offset(0, 2).Value
With Sheets("Sheet1")
.Range(.Cells(iStartRow, 1), .Cells(iEndRow, 1)) = sName
End With
Next 'c
End With

I like Debra's solution but note that with the last entry if you drag down
too far you'll get more of that name than you want.

Regards

Trevor


"Trevor Shuttleworth" wrote in message
...
Derek

one way:

Dim aParameters
Dim sName As String
Dim iStartRow As Integer
Dim iEndRow As Integer
Dim c As Range

'If LCase(ActiveSheet.Name) < "sheet2" Then Exit Sub ' or
If LCase(ActiveSheet.Name) < "sheet2" Then Sheets("Sheet2").Select
With Sheets("Sheet2")
For Each c In .Range(Range("A1"), .Range("A1").End(xlDown))
aParameters = Split(c.Value, ",")
sName = aParameters(0)
iStartRow = aParameters(1)
iEndRow = aParameters(2)
With Sheets("Sheet1")
.Range(.Cells(iStartRow, 1), .Cells(iEndRow, 1)) = sName
End With
Next 'c
End With

Regards

Trevor


"Derek Y via OfficeKB.com" wrote in message
...

Hello,

In sheet2 of an excel I would to have 3 Columns. Name, Start row, End
Row.

I would for example plug in

Derek, 2, 400
Bob, 401, 700
Jane,701,1000

Where my comma's denote the next column.

I would then like Derek entered in column A row 2, 3......400 (in
sheet1).
Then I want Bob to be entered in column A, row 401,402...700. You get
the
point. Is this possible?

Thanks in advance,
Derek

Thats beautiful Debra. Thank You. Thank you also trevor, but it looks like
what you wrote was vbase and I have no idea where I would even enter that
stuff or how to run it, etc.

Thanks\\

Debra Dalgleish wrote:
On Sheet 1, in cell A2, enter the formula:

=INDEX(Sheet2!$A$1:$A$20,MATCH(ROW(),Sheet2!$B$1:$ B$20,1))

Copy down to row 1000

Hello,

[quoted text clipped - 14 lines]
Thanks in advance,
Derek



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

You're welcome. Thanks for letting me know that the formula helped.

Derek Y via OfficeKB.com wrote:
Thats beautiful Debra. Thank You. Thank you also trevor, but it looks like
what you wrote was vbase and I have no idea where I would even enter that
stuff or how to run it, etc.

Thanks\\

Debra Dalgleish wrote:

On Sheet 1, in cell A2, enter the formula:

=INDEX(Sheet2!$A$1:$A$20,MATCH(ROW(),Sheet2!$B$1:$ B$20,1))

Copy down to row 1000



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Derek

glad you got an answer that worked for you ... courtesy of Debra.

Post back if you ever want to know how to use the VBA version. It's not as
difficult as it might seem at first. I have to say though, if you can get
by with formulae, it's probably best, particularly if other people use the
workbook.

Regards


"Derek Y via OfficeKB.com" wrote in message
...

Thats beautiful Debra. Thank You. Thank you also trevor, but it looks
like
what you wrote was vbase and I have no idea where I would even enter that
stuff or how to run it, etc.

Thanks\\

Debra Dalgleish wrote:
On Sheet 1, in cell A2, enter the formula:

=INDEX(Sheet2!$A$1:$A$20,MATCH(ROW(),Sheet2!$B$1:$ B$20,1))

Copy down to row 1000

Hello,

[quoted text clipped - 14 lines]
Thanks in advance,
Derek



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1