Take a look at our
ThinkPads.com HOME PAGE
For those who might want to contribute to the blog, start here: Editors Alley Topic
Then contact Bill with a Private Message
ThinkPads.com HOME PAGE
For those who might want to contribute to the blog, start here: Editors Alley Topic
Then contact Bill with a Private Message
For any Excel gurus out there
-
- ThinkPadder
- Posts: 1451
- Joined: Fri Oct 11, 2013 7:45 pm
- Location: North Ridgeville, Ohio, United States
- Contact:
For any Excel gurus out there
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?
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?
T480 with T25 keyboard | T25 | W520 i7-2860QM·Quadro 2000m·IPS FHD | T601F T9900·NVS 140m·LED AFFS UXGA
T420 IPS FHD | X220 IPS FHD | T61p·T61·43·42p|X13 Yoga G3·220T·301·41T·24·23·22|G41|A31p·22m|i1200|TransNote
600|770Z|770|760XD|760EL|701C|755C
T420 IPS FHD | X220 IPS FHD | T61p·T61·43·42p|X13 Yoga G3·220T·301·41T·24·23·22|G41|A31p·22m|i1200|TransNote
600|770Z|770|760XD|760EL|701C|755C
-
- Admin Emeritus
- Posts: 23812
- Joined: Mon Sep 18, 2006 5:17 am
- Location: Loch Garman, Éire
Re: For any Excel gurus out there
Try and concatenate the commands with an Ampersand ( & ) between the functions (cmd1 & cmd2 & cmd3).
Lovely day for a Guinness! (The Real Black Stuff)
Lenovo: X240, X250, T440p, T480, M900 Tiny.
PS: the old Boardroom website is still available on the Wayback Machine.
Lenovo: X240, X250, T440p, T480, M900 Tiny.
PS: the old Boardroom website is still available on the Wayback Machine.
-
- ThinkPadder
- Posts: 1451
- Joined: Fri Oct 11, 2013 7:45 pm
- Location: North Ridgeville, Ohio, United States
- Contact:
Re: For any Excel gurus out there
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.
T480 with T25 keyboard | T25 | W520 i7-2860QM·Quadro 2000m·IPS FHD | T601F T9900·NVS 140m·LED AFFS UXGA
T420 IPS FHD | X220 IPS FHD | T61p·T61·43·42p|X13 Yoga G3·220T·301·41T·24·23·22|G41|A31p·22m|i1200|TransNote
600|770Z|770|760XD|760EL|701C|755C
T420 IPS FHD | X220 IPS FHD | T61p·T61·43·42p|X13 Yoga G3·220T·301·41T·24·23·22|G41|A31p·22m|i1200|TransNote
600|770Z|770|760XD|760EL|701C|755C
-
- Admin Emeritus
- Posts: 23812
- Joined: Mon Sep 18, 2006 5:17 am
- Location: Loch Garman, Éire
Re: For any Excel gurus out there
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.
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.
-
- Senior Member
- Posts: 852
- Joined: Sun Feb 26, 2012 2:25 am
- Location: Princeton, New Jersey
- Contact:
Re: For any Excel gurus out there
Try this formula:
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.
Code: Select all
INDIRECT("Sheet1!C" & (ROUNDDOWN(ROW()-1 / 3) + 1))
Although I must say using Excel to generate scripts is slightly weird.
X60 tablet 6363-P3U, 3GB ram, 128GB SanDisk Extreme SSD, SXGA+ screen, Intel 6300
T61 Frankenpad in 15 inch T60 body, UXGA LED-lit AFFS LCD, T9300, 6GB RAM, NVidia NVS140m, Intel 6205, 128GB Crucial M4 SSD, 1TB HGST HDD + eBay caddy in Ultrabay
701c butterfly, 75MHz 486DX4, 40MB ram, 1GB CF card
T61 Frankenpad in 15 inch T60 body, UXGA LED-lit AFFS LCD, T9300, 6GB RAM, NVidia NVS140m, Intel 6205, 128GB Crucial M4 SSD, 1TB HGST HDD + eBay caddy in Ultrabay
701c butterfly, 75MHz 486DX4, 40MB ram, 1GB CF card
-
- Similar Topics
- Replies
- Views
- Last post
-
-
T440p + Ultra Dock, but dock video out (HDMI) not working
by Arf the Lab » Sat Nov 04, 2023 6:58 pm » in ThinkPad T430-T490 / T530-T590 Series - 6 Replies
- 4651 Views
-
Last post by RealBlackStuff
Mon Nov 06, 2023 1:39 am
-
-
-
WTB: Thinkpad T420 (Maxed out) i7.
by Thinkbookpro » Thu Jan 18, 2024 7:00 pm » in Marketplace - Forum Members only - 1 Replies
- 814 Views
-
Last post by BillMorrow
Sun Jan 21, 2024 5:05 am
-
-
-
Quick request for any owners of a T480- with an MX150 GPU.
by Dossing Around » Mon Oct 16, 2023 2:58 pm » in ThinkPad T430-T490 / T530-T590 Series - 0 Replies
- 4231 Views
-
Last post by Dossing Around
Mon Oct 16, 2023 2:58 pm
-
-
-
600e won't take any ram sticks
by Gonzaleitor » Mon Oct 23, 2023 11:30 pm » in ThinkPad Legacy Hardware - 2 Replies
- 4654 Views
-
Last post by Gonzaleitor
Tue Oct 24, 2023 9:50 am
-
Who is online
Users browsing this forum: No registered users and 39 guests