Auto pulling data from added sheets

[hotdogs1999]

Hello,

Wondering if it is possible to auto pull info from a new sheet when it is added - maybe would need to use VBA?

eg. Sheet 1 is "MASTER", Sheet 2 is "MEMO 1.0", Sheet 3 is "MEMO 2.0"... Sheet 10 is "MEMO 9.0" etc. (all tabs after the Master one will be "MEMO x.0"

each "MEMO" sheet will have that same layout so i will always be pulling the same cells from each one.
e.g Cell E7 from "MEMO 1.0" shows in Cell A3 in "MASTER", then Cell E7 from "MEMO 2.0" shows in Cell A4 in "MASTER"

will be pulling data from about 10 cells on each "MEMO" sheet - but it isn't sequential e.g. A7, C14, B5 etc. (though it will be exactly the same for each "MEMO" sheet)
these cells would pull through to the "MASTER" sheet into e.g. A3, B3, C3, D3 etc.
but each "MEMO" sheet would be on the next row down.
and when we added another sheet e.g. "MEMO 10.0" it would need to automatically pull the same cells through to the next row down on the "Master" sheet.

a bit complex....

thanks in advance

 

[Toadstool]

Hi Hotdogs1999,

You can use an old internal macro GET.WORKBOOK to retrieve the sheet names by defining a name (mine is AllMySheets) and an INDEX to retrieve each names. I use column J in MASTER but you can put it anywhere. Then use INDIRECT to access E7 on each sheet (for my test I created 4 sheets with "This is1" then "This is 2" in E7 of each).

Book1
	A	B							I	J
1										Sheet Names Below
2										MASTER
3	This is 1									Memo 1.0
4	This is 2									Memo 2.0
5	This is 3									Memo 3.0
6	This is 4									Memo 4.0
MASTER

Cell Formulas
Range	Formula
A3:A6	A3	=IFERROR(INDIRECT("'"&J3&"'!e7"),"")
J2:J6	J2	=IFERROR(INDEX(AllMySheets,ROWS($A$1:$A1)),"")

 

[hotdogs1999]

thanks,

could you give a slightly more step by step process to set this up - sorry i'm an amateur when it comes to this sort of thing

 

[hotdogs1999]

.

 

[Toadstool]

Hi HotDogs1999,

OK, let me go for a simpler solution which relies on your assertion "all tabs after the Master one will be "MEMO x.0"".

INDIRECT will use whatever is in the () to make an address and retrieve that value.
If I have "This is 4" in cell E7 of sheet "MEMO 4.0" then I'll need to construct "'Memo 4.0'!E7" (the single apostrophe is needed by Excel because you have a space in the sheet name).

COLUMN() returns the current column number so if I put it anywhere in column A it gives a 1, B gives 2, C gives 3 and D gives 4, so we can use that to get the sheet number part.

If I put in D3 =INDIRECT("'MEMO "&COLUMN()&".0'!e7") it will give me the contents of "'Memo 4.0'!E7" which is what I want. If I copy that across all columns where I want answers then in my test data I don't have "MEMO 5.0" so it will give me a #REF! error, so I can wrap IFERROR(,"") around it to get null ("") returned.

Putting this all together I should enter
=IFERROR(INDIRECT("'MEMO "&COLUMN()&".0'!e7"),"")
into cell A3 then copy right the number of columns needed for the maximum sheets I'll ever need.

My test workbook only has 5 sheets, MASTER and MEMO 1.0 through MEMO 4.0

So to populate row 3 in MASTER I use this:

HotDogs1999-v2.xlsx
	A	B	C	D	E	F
2
3	This is 1	This is 2	This is 3	This is 4
4
MASTER

Cell Formulas
Range	Formula
A3:F3	A3	=IFERROR(INDIRECT("'MEMO "&COLUMN()&".0'!e7"),"")

 

[hotdogs1999]

Thanks very much.

is there a simple change to make this go vertical? at the moment you have the data from each sheet showing beside each other across the columns, is it possible to have that going down the rows instead e.g. A3 (Memo1.0), A4 (Memo 2.0), A5 (Memo 3.0 etc.

Thanks again

 

[Toadstool]

Your original post had " e.g. A3, B3, C3, D3 etc. " so I thought you wanted horizontal. If you want vertical then we just need a different way of calculating the sheet number.

If we want MEMO 1.0 in A3 then we can use the row number minus two to calculate:
=IFERROR(INDIRECT("'MEMO "&ROW()-2&".0'!e7"),"")

HotDogs1999-v3.xlsx
	A
3	This is 1
4	This is 2
5	This is 3
6	This is 4
7
8
MASTER

Cell Formulas
Range	Formula
A3:A8	A3	=IFERROR(INDIRECT("'MEMO "&ROW()-2&".0'!e7"),"")

 

[hotdogs1999]

thanks,

that works great, but only up to 10 for some reason. e.g. when i add MEMO 11.0, 12.0 etc. it just pulls through "0" instead of the value. do you know why this might be?

 

[Toadstool]

Are you sure E7 has a value on those sheets and the sheet names exactly match your format?

It works OK for me

 

[hotdogs1999]

good call. fixed now.

Thanks very much