Index on other sheets.

[Xylia]

Hey everyone,

I am sure I am over complicating a simple task, so if there is an easy way I am all ears.

I have 4 separate tables I have been asked to add in an attendance column, if attendance is less than 90% I want ID, child's name, school and attendance pulled into a new table. So far I have managed to do this from only 1 table and only if it is on the same sheet. Is there a way to a) include the other 3 tables and b) have my new table on a separate sheet.

the formula I am currently using is

=INDEX($A$2:$E$120,SMALL(IF((INDEX($A$2:$E$120,,$I$4)<=$I$3)*(INDEX($A$2:$E$120,,$I$4)>=$I$2),MATCH(ROW($A$2:$E$120),ROW($A$2:$E$120)),""),ROWS(H14:$H$14)),COLUMNS($M$4:M4))

Thanks a lot in advance.

 

[etaf]

how about using

=VSTACK(FILTER(Sheet2!A2:D4,Sheet2!D2:D4<0.9),FILTER(Sheet3!A2:D4,Sheet3!D2:D4<0.9),FILTER(Sheet4!A2:D4,Sheet4!D2:D4<0.9),FILTER(Sheet5!A2:D4,Sheet5!D2:D4<0.9))

Book7
	A	B	C	D
1	ID	Child	School	attendance
2	sheet2-1	sheet2-a1	a1	0.1
3	sheet2-3	sheet2-a3	a3	0.6
4	sheet3-1	sheet3-a1	a1	0.1
5	sheet3-3	sheet3-a3	a3	0.6
6	sheet4-1	sheet4-a1	0	0.1
7	sheet4-3	sheet4-a3	0	0.6
8	sheet5-1	sheet5-a1	0	0.1
9	sheet5-3	sheet5-a3	0	0.6
Sheet1

Cell Formulas
Range	Formula
A2:D9	A2	=VSTACK(FILTER(Sheet2!A2:D4,Sheet2!D2:D4<0.9),FILTER(Sheet3!A2:D4,Sheet3!D2:D4<0.9),FILTER(Sheet4!A2:D4,Sheet4!D2:D4<0.9),FILTER(Sheet5!A2:D4,Sheet5!D2:D4<0.9))
Dynamic array formulas.

school-attendance-ETAF.xlsx
	A	B	C	D
1	ID	Child	School	attendance
2	sheet2-1	sheet2-a1	a1	0.1
3	sheet2-2	sheet2-a2	a2	1
4	sheet2-3	sheet2-a3	a3	0.6
Sheet2

school-attendance-ETAF.xlsx
	A	B	C	D
1	ID	Child	School	attendance
2	sheet3-1	sheet3-a1	a1	0.1
3	sheet3-2	sheet3-a2	a2	1
4	sheet3-3	sheet3-a3	a3	0.6
Sheet3

same on other sheets

dropbox version
will only be available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Xylia]

Hey thanks for your responses, I am not great at this but I'm keen to learn. As I am on my work PC i can't download anything but here is some fake data as you will see we have other information that we need to manage and we don't want to duplicate information - hence I want this data on a different sheet. I hope this is clear but if you need more information please just ask.

Thanks again.

Test data.xlsx

Attendance Childs name ,Year ,School ,Attendance 90%

docs.google.com

 

[etaf]

how about

=VSTACK(FILTER(Data1!A2:D39,(Data1!D2:D39<90)*(Data1!A2:A39<>"")),FILTER(Data2!A2:D39,(Data2!D2:D39<90)*(Data2!A2:A39<>"")),FILTER(Data3!A2:D39,(Data3!D2:D39<90)*(Data3!A2:A39<>"")),FILTER(Data4!A2:D39,(Data4!D2:D39<90)*(Data4!A2:A39<>"")))

Test data-ETAF.xlsx
	A	B	C	D
2	Clark Kent	0	Cake	89
3	Robert Bruce Banner	8	Cake	74
4	Anthony Edward Stark	9	Cake	56
5	Pietro Maximoff	7	Cake	55
6	Lucas Lawson	5	Cucumber	84
7	Henry Jonathan	5	Cucumber	74
8	Janet van Dyne	5	Cucumber	77
9	Thor Odinson	7	Cucumber	62
10	Richard Milhouse	7	Cucumber	89
11	Steven Grant Rogers	7	Cucumber	88
12	Jacques Duquesne	6	Lemon	52
13	Victor Shade	6	Lemon	15
14	Natalia Alianovna Romanova	4	Lemon	35
15	Henry P. McCoy	2	Lemon	44
16	Heather Douglas	2	Lemon	49
17	Patsy Walker	1	Lemon	69
18	Simon Williams	1	Lemon	79
19	Zackary Bentley	0	Kiwi	89
20	Mckayla Dyer	8	Kiwi	74
21	Dominique Sullivan	9	Kiwi	56
22	Dashawn Owen	7	Kiwi	55
23	Gabriella Galvan	5	Kiwi	84
24	Holden Ferrell	5	Kiwi	74
25	Taylor Murray	5	Kiwi	77
26	Aria Kemp	7	Kiwi	62
27	Kamryn Cobb	7	Kiwi	89
28	Tristin Roberson	7	Kiwi	88
29	Leila Cherry	6	Kiwi	52
Attendance

Cell Formulas
Range	Formula
A2:D69	A2	=VSTACK(FILTER(Data1!A2:D39,(Data1!D2:D39<90)*(Data1!A2:A39<>"")),FILTER(Data2!A2:D39,(Data2!D2:D39<90)*(Data2!A2:A39<>"")),FILTER(Data3!A2:D39,(Data3!D2:D39<90)*(Data3!A2:A39<>"")),FILTER(Data4!A2:D39,(Data4!D2:D39<90)*(Data4!A2:A39<>"")))
Dynamic array formulas.

dropbox version - only available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I have also added a comparison in F,G,H,i

 

[Fluff]

Thanks for that, how about

=LET(d,TAKE(VSTACK(Table1,Table135,Table13,Table134),,4),FILTER(d,INDEX(d,,4)<90))

 

[Xylia]

That would have been perfect!!! however i have just checked and we don't have office 356 we have 2019 and it doesn't have the =vstack and i don't seem to be able to download it.. ****!

 

[Xylia]

I have done it!!! thank you so much for your help

 

[etaf]

would you mind posting the formula you used , it may help others who are searching the forum or google who need a similar solution and dont have the later versions of excel