Hello,
I'm trying to create a spread sheet that does a few functions, 3 exactly on 3 lines, that reference a single line of information. When I drag to auto complete, it auto completes every 3 lines. I know the $ command to lock the reference for auto complete but not sure how to tell the auto complete to only increase one line every 4th line.
Thanks!
If I am understanding you request correctly, you can use OFFSET (or INDEX) to change the row references every x number of rows.
If you require more specific help, please upload a small representative sample of your data along with the desired result (manually entered) of a formula.
To upload a sample Excel workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I want to autofill down 4 lines, but have the information that is referenced only look at the next line. Right now, it looks at the respective line for reference, which only grabs reference every 4 lines. I played with offset but it didn't seem to autofill correctly.
Thanks again!
Last edited by craigbrickner; 11-06-2017 at 12:05 PM.
Please provide a cut-down version of the massive file directly here. Not everyone is able or willing to access file-sharing sites or download massive files.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The description of what you are looking to do is still not clear to me.
Note in post #2, you were asked for a "small representative sample" of your data. We do not need to see your entire workbook. Many contributors, including myself, can not and/or will not download files from 3rd party sites due to security concerns.
Simply recreate a few rows of data to show us the general layout of your data along with what you expect from a formula.
Stripped down example attached! Thank you everyone!
Data requested is:
[1]
Connect=ssh 1.1.1.1
EthernetDHCPHost USCHIHPROC1
[2]
Connect=ssh 1.1.1.2
EthernetDHCPHost USCHIHPROC2
[3]
Connect=ssh 1.1.1.3
EthernetDHCPHost USCHIHPROC3
[4]
Connect=ssh 1.1.1.4
EthernetDHCPHost USCHIHPROC4
Data actual is:
[1]
Connect=ssh 1.1.1.1
EthernetDHCPHost USCHIHPROC1
[5]
Connect=ssh 1.1.1.5
EthernetDHCPHost USCHIHPROC5
[9]
Connect=ssh 1.1.1.9
EthernetDHCPHost USCHIHPROC9
[13]
Connect=ssh 1.1.1.13
EthernetDHCPHost USCHIHPROC13
Try this in Sheet1:
A1 =IFERROR("["&INDEX(Sheet2!A$2:A$19,INT((ROWS(Sheet2!A$1:A1)-1)/4+1))&"]","")
A2 =IFERROR("Connect=ssh "&INDEX(Sheet2!B$2:B$19,INT((ROWS(Sheet2!A$1:A1)-1)/4+1)),"")
A3 =IFERROR("EthernetDHCPHost USCHIHPROC"&INDEX(Sheet2!A$2:A$19,INT((ROWS(Sheet2!A$1:A1)-1)/4+1)),"")
Leave A4 blank.
Then, highlight A1:A4 and drag the formulas down until you get blank rows.
See attachment for clarification.
Last edited by 63falcondude; 11-06-2017 at 12:17 PM.
That's awesome! Thank you 63falcondude!
You're welcome. Happy to help.
I got it working with your initial post...should I use the amended post?
I guess that you saw it before I finished the edit. The edited version ensures that no results (in Sheet1) is produced after the last number (18 in this case).
With the edited formulas, you can drag the formulas down as far as you want without seeing unexpected/junk results.
They will both work fine but I would use the edited formulas if this is more than a one-time task.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks