Automatically Enter Data

[Derek Y via OfficeKB.com]

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

 

[Derek Y via OfficeKB.com]

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

 

[Trevor Shuttleworth]

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

 

[Debra Dalgleish]

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

 

[Trevor Shuttleworth]

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

 

[Derek Y via OfficeKB.com]

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\\

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

 

[Debra Dalgleish]

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

 

[Trevor Shuttleworth]

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

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\\

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