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

 

[hotdogs1999]

hey, just a last question. when any of the cells noted in the formula are blank, it puts a '0' value in the Master Sheet cell instead of leaving it blank. any thoughts?

 

[Toadstool]

You could force it to make it text instead of numeric by add in null
=IFERROR(INDIRECT("'MEMO "&ROW()-2&".0'!e7"),"")&""
...but that may impact other calculations on your MASTER sheet.

You could use an IF statement to test it first:
=IFERROR(IF(INDIRECT("'MEMO "&ROW()-2&".0'!e7")="","",INDIRECT("'MEMO "&ROW()-2&".0'!e7")),"")

 

[hotdogs1999]

thanks very much

 

[hotdogs1999]

just wondering for the sheet names - you have referenced in the formula "MEMO" as i said earlier that all tabs would be called this.

is it possible to put an OR option in there, so the name could be "MEMO" or "DEFECT" and it would still work?

sorry to be a bother

 

[Toadstool]

No, there's no simple OR approach to this but I need to ask some questions first:

1. Do the DEFECT tabs have the same naming convention, i.e. "DEFECT 3.0", "DEFECT 12.0"?
2. Could there be a "MEMO 8.0" tabs and a "DEFECT 8.0" tab? If so we can no longer use the row number to calculate.
3. Would you be OK with having a new column A to contain the tab names? It would make the formulae to retrieve the 10 cells you want somewhat simpler.
4. Approximately how many MEMO and DEFECT tabs will you have?

 

[hotdogs1999]

No, there's no simple OR approach to this but I need to ask some questions first:

1. Do the DEFECT tabs have the same naming convention, i.e. "DEFECT 3.0", "DEFECT 12.0"?
2. Could there be a "MEMO 8.0" tabs and a "DEFECT 8.0" tab? If so we can no longer use the row number to calculate.
3. Would you be OK with having a new column A to contain the tab names? It would make the formulae to retrieve the 10 cells you want somewhat simpler.
4. Approximately how many MEMO and DEFECT tabs will you have?

1. Yes they would have the same naming convention
2. if that was possible to have a Defect and Memo tab with the same number it would be good - but not totally essential if too annoying
3. yes another column with tab names would be fine - are you thinking to pull them in with a formula, or pre load them?
4. i would reckon about 20max of each for a single project - usually less.

 

[Toadstool]

OK, the easiest solution would be the very first I gave which pulls in the actual sheet names, but that means saving as an .xlsm and enabling macros. This solution just uses Functions.

I've created a new tab "Wrk". It can be hidden after creation as you don't need to see it.
Column A is all possible sheet names for MEMO and DEFECT up to 100 (so formula copied down 200 rows).
Column B then gets the sheet number so if that name exists you'll see a number, otherwise an empty cell.

HotDogs1999-v5.xlsx
	A	B
1	Possible Sheets	Sheet Number
2	MEMO 1.0	3
3	DEFECT 1.0
4	MEMO 2.0	4
5	DEFECT 2.0
6	MEMO 3.0	5
7	DEFECT 3.0
8	MEMO 4.0	6
9	DEFECT 4.0
10	MEMO 5.0	7
11	DEFECT 5.0	8
12	MEMO 6.0
13	DEFECT 6.0
14	MEMO 7.0	9
Wrk

Cell Formulas
Range	Formula
A2:A14	A2	=IF(ROW()/2>INT(ROW()/2),"DEFECT "&(INT(ROW()/2))&".0","MEMO "&(INT(ROW()/2))&".0")
B2:B14	B2	=IFERROR(SHEET(INDIRECT("'"&A2&"'!a1")),"")

For the MASTER sheet the new column A uses Wrk to retrieve any sheet names which have a sheet number.
Column B then uses the INDIRECT as previously discussed to retrieve the contents of E7 from each sheet.

HotDogs1999-v5.xlsx
	A	B
2
3	MEMO 1.0	This is 1
4	MEMO 2.0	This is 2
5	MEMO 3.0	This is 3
6	MEMO 4.0	This is 4
7	MEMO 5.0	ABC
8	DEFECT 5.0	ABC
9	MEMO 7.0	ABC
10	MEMO 9.0	ABC
11	DEFECT 9.0	ABC
12	MEMO 10.0	ABC
13	MEMO 11.0	ABC
14
MASTER

Cell Formulas
Range	Formula
A3:A14	A3	=IFERROR(INDEX(Wrk!$A$2:$A$200,AGGREGATE(15,6,ROW(Wrk!$A$2:$A$200)-ROW(Wrk!$A$1)/(Wrk!$B$2:$B$200<>""),ROW()-2)),"")
B3:B14	B3	=IFERROR(IF(INDIRECT("'"&A3&"'!e7")="","",INDIRECT("'"&A3&"'!e7")),"")

 

[hotdogs1999]

what if we kept the current "Memo 1.0" and "Memo 2.0" system and added "defect" to the end of occasional ones. e.g. "MEMO 1.0" "MEMO 2.0 (defect)" "MEMO 3.0"
would it be possible to somehow tweak the formula i am currently using to work with this?

 

[Toadstool]

You can use a wildcard as I have with the two asterisks here:
=IFERROR(IF(INDIRECT("'MEMO "&ROW()-2&".0*'!e7")="","",INDIRECT("'MEMO "&ROW()-2&".0*'!e7")),"")
...but if you have a "MEMO 6.0" and a "MEMO 6.0 (defect)" it will only access the first one.

 

[hotdogs1999]

thanks, that works great.