For any Excel gurus out there

[theterminator93]

So I set up a spreadsheet to generate a script that will create a folder structure for a task at work with appropriate permissions etc. There are two sheets; sheet one has a couple rows with the names of the users, then a formula generates the username of the user. Sheet two generates the script that creates the folders and sets NTFS permissions - a three line per user process.

The problem I ran into is that since I have three rows of formula in sheet two, all of which reference the same row on sheet one, that when I copy the three rows and paste them down to however many rows I need for the number of users/folders I'm setting up, it increments by three and skips two user rows on sheet one.

Example sheet1:
a b c
1 first last username
2 joe smith jsmith
3 jack bauer jbauer
4 john doe jdoe
5 billy smith bsmith

Example sheet2 output:
a b c
1 mkdir "example\jsmith"
2 icacls "example\jsmith" /grant:jsmith r
3 icacls "example\jsmith" /grant:jsmith (oi)(ci)(io)m
4 mkdir "example\bsmith"
etc.

As you can see, if I copy/paste the three rows of formula from sheet 2 for my script it skips two rows on sheet 1. I got around this by adding two blank rows between each user on sheet one for now, but is there a way to get excel to auto increment the cell reference in the formula by one when copying and pasting three rows of formula?

[RealBlackStuff]

Try and concatenate the commands with an Ampersand ( & ) between the functions (cmd1 & cmd2 & cmd3).

[theterminator93]

Wouldn't that put them all in the same cell though? I'm not opposed to that if there's a way to get the three commands on separate lines in that cell when I export the sheet.

[RealBlackStuff]

Try this:
put the Cursor in that cell, click on Format/Format Cells, click on the Alignment tab.
Set the Alignment text fields to:
- Horizontal Left
- Vertical Top
- blank
Set Text control to:
- Wrap text
- blank
- blank
Now make the column as wide as the longest command.

[twistero]

Try this formula:

Code: Select all

INDIRECT("Sheet1!C" & (ROUNDDOWN(ROW()-1 / 3) + 1))

This should give you the contents of Sheet1!C1 when placed in rows 1-3, Sheet1!C2 when in rows 4-6, etc.

Although I must say using Excel to generate scripts is slightly weird.