Excel Autofill problem 1

[bluekestrel]

I have a summary sheet with several sheets of data behind.
The column headings on both summary sheet and data sheets are week commencing dates.

On the summary sheet I have a list of several hundred customers and below each customer name are 6 rows of data pulled from 6 seperate data sheets.

eg
customer A
DataA (feeds from sheet2 C2)
DataB (feeds from sheet3 C2)
DataC (feeds from sheet4 C2)
DataD (feeds from sheet5 E150)
DataE (feeds from sheet6 C2)
DataF (feeds from sheet7 C2)
Customer B
DataA (feeds from sheet2 C3)
DataB (feeds from sheet3 C3)
DataC (feeds from sheet4 C3)
DataD (feeds from sheet5 E151)
DataE (feeds from sheet6 C3)
DataF (feeds from sheet7 C3)
etc
Unfortunately when I fill in several rows manually and then try and copy the rest down to autofill Excel does not follow the logic of my sequence and instead of Sheet2 C4 it will add Sheet2 C10

I understand why it does this but I don't know how to make it do what I want, there are too many entries to try and do this manually.

Please help !! Thanks in advance

 

[01Patrik]

In this case, when ít's always 7 rows between, you can use

Customer A
Data A =indirect("sheet2!C"&1+roundup(row(A1)/7))
Data B =indirect("sheet3!C"&1+roundup(row(A1)/7))
Data C =indirect("sheet4!C"&1+roundup(row(A1)/7))
Data D =indirect("sheet5!E"&150+roundup(row(A1)/7))
Data E =indirect("sheet6!C"&1+roundup(row(A1)/7))
Data F =indirect("sheet7!C"&1+roundup(row(A1)/7))


Copy this down, and the problem should be solved.

// Patrik

 

[01Patrik]

Sorry, missed that all functions should end with /7),0)


The roundup part is unnecessary in this case, if you choose to copy "Data A" and autofill that one, then you will need the roundup.

Of course, you can also have a separate column with the sheet name:

Column A Column B
Customer A
DataA Sheet2
DataB Sheet3
DataC Sheet4
DataD Sheet5
DataE Sheet6
DataF Sheet7

in this case, use the following function in A2 ("DataA")

=indirect("'"&B2&'"!C"&1+roundup(row(A1)/7))

and autofill down.


// Patrik

 

[bluekestrel]

Patrik

Thank you very much that has done the job nicely and saved me hours of work - incidently the solution I used was your first post and I didn't need the amendment /7),0) (in actual fact changing to this caused an error)

Thanks again