Formula Fill Series

[yeninsoutheast]

Hello, I am working on a spreadsheet and I need values from Sheet1!a2, a7, a12, a17 etc...

I want to fill in the formula ='Sheet1'!a2 etc, to fill column A in sheet2!

When I highlight and drop down to fill excel fills the column in increments of five like i need but doesnt start on the right one. Instead it fills the formula as ='Sheet1'!a6, (For cel A1) and ='Sheet1'!a11, (for cell A2) and so on. Can excel perform this function am I explaining it clearly? I have done a lot of reading on line and I can't seem to find an answer, please help.

[yeninsoutheast]

Maybe this will explain better:

When I highlight Formulas ='Sheet1'!A2
='Sheet1'!A7
='Sheet1'!A12

and pull the drop down I want to get: ='Sheet1'!A17
='Sheet1'!A22
='Sheet1'!A27 etc......

but what I get is ='Sheet1'!A5
='Sheet1'!A10
='Sheet1'!A15

[AlKey]

Hi yeninsoutheast and welcome to the forum.

I think the best way to explain your needs would be to upload a spreadsheet where you can show what the data is and show as what the expected result should be.

To upload a file click on Go Advanced on the bottom of your screen, then click on paperclip icon and Add Files.

[yeninsoutheast]

so i want the information from Art Log! c2, c7, c12 etc....
to be copied to Inventory! A2, A3, A4

I know it can be done manually by entering the formula ='Art Log'!C2 in each cell on the
Inventory work sheet, but I was hoping it would be copied in increments of 5 cells if I just
to use the fill handle after entering 4 cells of the desired increment formula. But it does
start on the right cell

[AlKey]

There is no Art Log sheet in the workbook you have provided and there is no Inventory either. Did you upload wrong workbook?

[yeninsoutheast]

sorry about that here is the correct sheet

[yeninsoutheast]

I really appreciate the help AlKey! any light can be helpful

[yeninsoutheast]

I have been playing with the work sheet and when i try to copy a simple =C2, =C7, =C12 with the drop down handle it does not complete the series, it says inconsistent formula, and starts the series from =C6 or another seemingly random starting point instead of continuing in the series, the frustrating thing is that it copies in the right increments of cells, if I can only get it to start at the right point

[AlKey]

On your Inventory sheet in cell A2 copy and paste formula and drag it down

=INDIRECT("'Art Log'!"&"C"&ROW(2:2)*5-8)

[yeninsoutheast]

I can play with the formula and figure it out for the other columns if you dont have the time but I have to do the same thing for all the columns in Inventory sheet,

[yeninsoutheast]

Wow that is amazing! THanks AlKey!

can you explain it to me how the formula works I have to do the same function with the date, and pricing. You have really helped me out a lot THANK YOU!! I couldnt find anything even close to this online, and I have been at this for two days! Do you have a business website or something?

[AlKey]

This one is for the Date

=INDIRECT("'Art Log'!"&"D"&ROW(2:2)*5-5)

Now, where is the pricing coming from?

[benishiryo]

hi there. here's another alternative:
=INDEX('Art Log'!C:C,2+(ROWS(A$2:A2)-1)*5)

i'll try to explain it so that you can grab whatever you need. type =INDEX(
- go to the worksheet you want to grab the data from & select the entire column you need
- type a comma for the next argument

2+ is because you want to start grabbing from row 2

(ROWS(A$2:A2)-1)*5 you also need an increment of 5 rows when copied down. so the first row (A2 in this case) should not have any increment of 5 rows. ROWS of A$2:A2 is the total rows the range has. A2:A2 is 1 row. so (1-1)*5 is 0. 0 increment
notice the dollar sign is locked at the row? that means if copy the formula down, it becomes A$2:A3. now the total rows this has is 2. (2-1)*5 is 5. so that increases it by 5 rows

hope that helps

[yeninsoutheast]

AlKey you are a real life saver!

I can definitely work with these formulas, you really changed my day.

Original Price is B6 on 'Art Log'!
Frame Cost is E5 on 'Art Log'!
Selling Price is E2 on 'Art Log'!

[yeninsoutheast]

Sorry benishiryo, I was reading and working so long I didn't even see that it was your post I thought it was AlKey. I appreciate the help and am truly surprised and grateful of how much I was able to get done today. Thank you both so much.

[AlKey]

Ok, here is the rest:

Original Price

=INDIRECT("'Art Log'!"&"B"&ROW(2:2)*5-4)

Frame Cost

=INDIRECT("'Art Log'!"&"E"&ROW(2:2)*5-5)

Selling Price

=INDIRECT("'Art Log'!"&"E"&ROW(2:2)*5-8)


Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[yeninsoutheast]

Sorry benishiryo, I was reading and working so long I didn't even see that it was your post I thought it was AlKey. I appreciate the help and am truly surprised and grateful of how much I was able to get done today. Thank you both so much.

[vlady]

FORUM MODERATOR'S REQUEST:

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.