TCCoA Forums banner

1 - 5 of 5 Posts

·
Super Moderator
Joined
·
9,357 Posts
Discussion Starter #1
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?
 

·
Super Moderator
Joined
·
5,005 Posts
With a C&P like that, no.

But you know, I think you can script what you're doing in Sheet 2 in Powershell. Save Sheet1 as a CSV. Or even just column 3 as a CSV table.

RwP
 

·
Registered
Joined
·
1,647 Posts
I have an MS cert in Excel...2003 lol. God that was so long ago, I dig the Jack Bauer example though haha.
 

·
Super Moderator
Joined
·
9,357 Posts
Discussion Starter #4
I might ditch the excel idea later on, I might use it more over the summer and my patience will dictate whether I write a different script with a loop to just read the folder/user account names from a csv and do all this. I just need to make sure I go over the .csv first to ensure there are no duplicate names etc.
 

·
Super Moderator
Joined
·
10,751 Posts
...I dig the Jack Bauer example though haha.
For geek cred, you always populate the "junk" data with something vaguely recognizable.

The only one I ever got called out on was "A. Schicklgruber, Berlin"

:)

Lotr, matrix, niven, heinlein; nothing's sacred. :)

I recently replaced the "lorem ipset" text in all our templates with the actual Cicero quote it mangles. :)
No one has noticed yet, and there are multiple Latin speakers in our group.
.
.

"Lorem ipsum" text is derived from sections 1.10.32–3 of Cicero's De finibus bonorum et malorum (On the Ends of Goods and Evils, or alternatively [About] The Purposes of Good and Evil).[2] The original passage began: Neque porro quisquam est qui dolorem ipsum quia dolor sit amet consectetur adipisci velit (translation: "Neither is there anyone who loves, pursues or desires pain itself because it is pain").​

.
In most templates, the bold text is repeated.
 
1 - 5 of 5 Posts
Top