How do I automatically fill in naming convention from one cell?

[zilla1126]

Basically I am creating spreadsheets to handle migrations of VM pools, and I need to be able to fill in the naming convention for the new computer names automatically on the second sheet, from a cell on the first sheet that has the naming convention, combined with another cell on the first sheet that has the number of computers.

So, on D4 on a sheet called "main", I put in the naming convention for the pool of computers I will be creating (ex: "newworkstation-").

On B7 on sheet main, is the number of machines to be in this pool (ex: 15).

On sheet VMs, I need to autopopulate the machine names in column B down, starting at B2.

Example output on sheet VMs, column B, starting at B2, using the example pool name of "newworkstation-", and pool size of 15:

B2 newworkstation-1
B3 newworkstation-2
B4 newworkstation-3
B5 newworkstation-4
B6 newworkstation-5
B7 newworkstation-6
B8 newworkstation-7
B9 newworkstation-8
B10 newworkstation-9
B11 newworkstation-10
B12 newworkstation-11
B13 newworkstation-12
B14 newworkstation-13
B15 newworkstation-14
B16 newworkstation-15

I have used in my examples the actual cell numbers and sheet names. I'm more interested in a simple solution than a complex one. I'm new to doing complex things with excel and I have not anything with VBA. I am familiar with making drop down lists using data verification and vlookup.

[Pete_UK]

Put this in B2:

=IF(ROW(B2)-1>main!$B$7,"",main!$C$5 & ROW(B2)-1)

then copy down as far as you like.

Hope this helps.

Pete

[zilla1126]

Thanks for the help. When I paste this in to B2, it gives me the correct computer name: newworkstation-1. Is there a way for excel to take the number of machines I need from B7 (form the sheet named "main") to populate the proper number of machines on the VMs sheet?

It will be a different number of machines for each pool I need to work with.

Thanks again.

[Pete_UK]

Copy the formula down to, say, B25 and see what happens.

Pete

EDIT: Then change the number in main!B7 to 20 and see what happens.

[zilla1126]

My forgot to note something in my previous reply, when I put this formula in B2 on the VMs sheet, I get this:

b2 newworkstation-2

If I paste it into b3, I get:

b3 newworkstation-2


>> Copy the formula down to, say, B25 and see what happens.

I know I can change the formula for each cell, but that is what I'm trying to avoid.

When I try that, I get the same output in each column:

b2 newworkstation-2
b3 newworkstation-2
b4 newworkstation-2
b5 newworkstation-2
b6 newworkstation-2
b7 newworkstation-2

Will I need to take a different approach altogether? If there is not a way for the formula

[Pete_UK]

I don't know what you are doing wrong - see attached mock-up of your problem.

Hope this helps.

Pete

[zilla1126]

That did it. Thanks for your help, I appreciate it.