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

For any Excel gurus out there

Talk about "WhatEVER !"..
Post Reply
Message
Author
theterminator93
ThinkPadder
ThinkPadder
Posts: 1451
Joined: Fri Oct 11, 2013 7:45 pm
Location: North Ridgeville, Ohio, United States
Contact:

For any Excel gurus out there

#1 Post by theterminator93 » Sat Mar 19, 2016 9:03 pm

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

RealBlackStuff
Admin Emeritus
Admin Emeritus
Posts: 23812
Joined: Mon Sep 18, 2006 5:17 am
Location: Loch Garman, Éire

Re: For any Excel gurus out there

#2 Post by RealBlackStuff » Sun Mar 20, 2016 7:21 am

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
.

theterminator93
ThinkPadder
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

#3 Post by theterminator93 » Sun Mar 20, 2016 9:26 pm

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

RealBlackStuff
Admin Emeritus
Admin Emeritus
Posts: 23812
Joined: Mon Sep 18, 2006 5:17 am
Location: Loch Garman, Éire

Re: For any Excel gurus out there

#4 Post by RealBlackStuff » Mon Mar 21, 2016 5:11 am

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
Senior Member
Senior Member
Posts: 852
Joined: Sun Feb 26, 2012 2:25 am
Location: Princeton, New Jersey
Contact:

Re: For any Excel gurus out there

#5 Post by twistero » Thu Mar 24, 2016 12:38 pm

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. :roll:
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

Post Reply
  • Similar Topics
    Replies
    Views
    Last post

Return to “Off-Topic Stuff”

Who is online

Users browsing this forum: No registered users and 39 guests