solution without using VBA. Sheet creation from master list and template

[leecavturbo]

Can this be done?
essentially my template references a row with 7 columns of data.
whilst i can simply create my many many sheets from the template with Copy,Create Copy, Move to end manually.
the sheets all reference the same row but i need each consecutive sheet to reference the next row down on the master sequentially for the data to be correct on each sheet.

Tia

 

[jasonb75]

Assuming data starts in A2, you could use something like

=INDEX(A$2:A$200,SHEET()+1)

Note that the SHEET() function requires Excel 2013 or newer. I expect that it is a volatile function as well, so a lot of formulas using it could result in slow response times due to the constant recalculation.

 

[leecavturbo]

thanks for a reply , however i'm clearly out of your league haha. in what cells in the template would i put that? i quickly put in 1 cell but it referenced the same sheet! ( the template sheet , while the data is in the master sheet )
that looks to reference range of a column rather than rows?
=INDEX(A$2:A$2,SHEET()+1)? in the cell to ref A2
=INDEX(B$2:B$2,SHEET()+1)?
=INDEX(C$2:C$2,SHEET()+1)?

 

[jasonb75]

It was just a guess as you didn't say where the original data was located, or where the formula will go.

SHEET() count the sheets from left to right, so if your template is in sheet 1 and you're adding the first row of data to sheet 2, then it would be -1 instead of +1 (sheets don't need to be numbered / named sheet 1, sheet 2. any name will work, the function counts the sheets).

A$2:A$100 should refer to the first of the 7 columns of data in the template. When you drag it right it will move to the second column, then third, etc.
The SHEETS() part will move down through the rows as you copy it to new sheets. In sheet 2, SHEETS()-1 will return the first row of data, in sheet 3 it will return the second row and so on.

 

[leecavturbo]

here's the sheet.
simply want a sheet created for every day entry and the corresponding row data to each created sheet.

https://drive.google.com/file/d/1_iJyPKuXl3taX8SpzQYZJz7GSZrSn3Dy/view?usp=sharing

 

[jasonb75]

I have a feeling that you might be making it more complicated than it is by over thinking it.

Think of it as a simple INDEX - MATCH formula, SHEETS is used in place of MATCH to get the row number.

In the Template sheet, enter the formulas as

=INDEX(Master!A:A,SHEET()-1) in the date box
=INDEX(Master!B:B,SHEET()-1) in B7

and so on for the remaining columns of data into the correct cells, you will see that it pulls the data for 24/9 into the template.
Create a copy of the template (move to end) and it will pull the data for 25/9, next copy will pull the data for 26/9 and so on.

Note that the master must always be the first tab on the left in order to avoid potential errors.
After creating a couple of copies of the template, try changing the order of the tabs (not the master) then check the results. You will see that the copy next to the master will always show the data from the first row in the master, the next one will show the second row and so on.

If this is not good for what you want, then you will need to use vba. This is the best that you will get with formulas.

 

[leecavturbo]

I have a feeling that you might be making it more complicated than it is by over thinking it.

Think of it as a simple INDEX - MATCH formula, SHEETS is used in place of MATCH to get the row number.

In the Template sheet, enter the formulas as

=INDEX(Master!A:A,SHEET()-1) in the date box
=INDEX(Master!B:B,SHEET()-1) in B7

and so on for the remaining columns of data into the correct cells, you will see that it pulls the data for 24/9 into the template.
Create a copy of the template (move to end) and it will pull the data for 25/9, next copy will pull the data for 26/9 and so on.

Note that the master must always be the first tab on the left in order to avoid potential errors.
After creating a couple of copies of the template, try changing the order of the tabs (not the master) then check the results. You will see that the copy next to the master will always show the data from the first row in the master, the next one will show the second row and so on.

If this is not good for what you want, then you will need to use vba. This is the best that you will get with formulas.

That's perfect saved me hundreds and hundreds of formula entry