Keeping formulas on second sheet accurate after making changes in the first sheet

[barnkat]

Forgive my fumbling, it's difficult to be clear on this (relatively novice user).

I have an exam spreadsheet. The first sheet (Scoring Report) contains columns with the program name (two different programs take the course, call them A and B), ID, last name, first name, first exam score, etc. The second and third sheets are specifically each for a single program (sheet 2 = Program A, sheet 3 = Program B). I have these two sheets set up to pull the students exam scores from the first sheet using the formula =Sheet1!B3

Is there specific formula I should be using to make sure that any changes on the first sheet are accurately reflected on the other sheets? One that will ensure that the information from the first sheet will accurately flow to the other sheets, even if I have to sort or make other physical changes to the first sheet?

[My IT Dept has restricted my ability to add plug-ins like XL2BB to Office, so I'll try to recreate the sheets.]

For example, if Sheet 1 looks like this

	A​	B​	C​	D​	E​	F​
1​	PROG​	ID​	Last Name​	First Name​	Exam​	Percentage​
2​	AZC	52197	Amos	Gladys	42	88%
3​	AZC	49810	Azari	Phil	44	92%
4​	AZC	43170	Watson	Robert	38	79%
5​	AZC	50843	Berry	Linda	40	83%
6​	AZC	4957	Vegas	Tony	48	100%
7​	AZC	4934	Shifley	Adam	48	100%
8​	AZC	8493	Hart	Liz	44	92%
9​	BTW	51867	Sabine	Melanie	46	96%
10​	AZC	49745	Ivan	Susan	42	88%
11​	BTW	50766	Parsons	Jamie	44	92%
12​	BTW	15821	Andrews	Stephen	44	92%
13​	AZC	4311	Burns	Dave	44	92%
14​	BTW	5103	Shine	Ray	42	88%

then Sheet 2 is accurate

	A​	B​	C​	D​	E​	F​
1​	PROG​	ID​	Last Name​	First Name​	Exam​	Percentage​
2​	AZC	52197	Amos	Gladys	42	88%
3​	AZC	49810	Azari	Phil	44	92%
4​	AZC	43170	Watson	Robert	38	79%
5​	AZC	50843	Berry	Linda	40	83%
6​	AZC	4957	Vegas	Tony	48	100%
7​	AZC	4934	Shifley	Adam	48	100%
8​	AZC	8493	Hart	Liz	44	92%
9​	AZC	49745	Ivan	Susan	42	88%
10​	AZC	4311	Burns	Dave	44	92%

But if I sort Sheet 1 alphabetically

	A​	B​	C​	D​	E​	F​
1​	PROG​	ID​	Last Name​	First Name​	Exam​	Percentage​
2​	AZC	52197	Amos	Gladys	42	88%
3​	BTW	15821	Andrews	Stephen	44	92%
4​	AZC	49810	Azari	Phil	44	92%
5​	AZC	50843	Berry	Linda	40	83%
6​	AZC	4311	Burns	Dave	44	92%
7​	AZC	8493	Hart	Liz	44	92%
8​	AZC	49745	Ivan	Susan	42	88%
9​	BTW	50766	Parsons	Jamie	44	92%
10​	BTW	51867	Sabine	Melanie	46	96%
11​	AZC	4934	Shifley	Adam	48	100%
12​	BTW	5103	Shine	Ray	42	88%
13​	AZC	4957	Vegas	Tony	48	100%
14​	AZC	43170	Watson	Robert	38	79%

then Sheet 2 returns the wrong information.

	A​	B​	C​	D​	E​	F​
1​	PROG​	ID​	Last Name​	First Name​	Exam​	Percentage​
2​	AZC	52197	Amos	Gladys	42	88%
3​	BTW	15821	Andrews	Stephen	44	92%
4​	AZC	49810	Azari	Phil	44	92%
5​	AZC	50843	Berry	Linda	40	83%
6​	AZC	4311	Burns	Dave	44	92%
7​	AZC	8493	Hart	Liz	44	92%
8​	AZC	49745	Ivan	Susan	42	88%
9​	BTW	51867	Sabine	Melanie	46	96%
10​	AZC	4957	Vegas	Tony	48	100%

I have a feeling that I'm overlooking the obvious. I freely admit that I don't have enough experience in Excel to know what to try or how to ask the question, so please be kind in your comments. Any help is greatly appreciated.

 

[MountainFog]

What version of excel are you using?

Can you explain your formula = Sheet1!B3? Looking at what you posted, that points to ID 49810. Maybe I don't follow what you are trying to say

If you are using 365, I would use the =filter function

 

[barnkat]

365 Enterprise Edition.

In the example, the formula in sheet 2 cell A2 is literally (without the quotes) "=Sheet!A2", which is what I get when I type an = then click on the cell on the first sheet that I want to pull to the cell in the second sheet, then press Enter. Here is what sheet 2 with the formulas in [brackets] in each cell.

	A​	B​	C​	D​	E​	F​
1​	PROG​	ID​	Last Name​	First Name​	Exam​	Percentage​
2​	AZC [=Sheet1!A2]	52197 [=Sheet1!B2]	Amos [=Sheet1!C2]	Gladys [=Sheet1!D2]	42 [=Sheet1!E2]	88% [=Sheet1!F2]
3​	AZC [=Sheet1!A4]	49810 [=Sheet1!B4]	Azari [=Sheet1!C4]	Phil [=Sheet1!D4]	44 [=Sheet1!E4]	92% [=Sheet1!F4]
4​	AZC [=Sheet1!A5]	50843 [=Sheet1!B5]	Berry [=Sheet1!C5]	Linda [=Sheet1!D5]	40 [=Sheet1!E5]	83% [=Sheet1!F5]
5​	AZC [=Sheet1!A6]	4311 [=Sheet1!B6]	Burns [=Sheet1!C6]	Dave [=Sheet1!D6]	44 [=Sheet1!E6]	92% [=Sheet1!F6]
6​	AZC [=Sheet1!A7]	8493 [=Sheet1!B7]	Hart [=Sheet1!C7]	Liz [=Sheet1!D7]	44 [=Sheet1!E7]	92% [=Sheet1!F7]
7​	AZC [=Sheet1!A8]	49745 [=Sheet1!B8]	Ivan [=Sheet1!C8]	Susan [=Sheet1!D8]	42 [=Sheet1!E8]	88% [=Sheet1!F8]
8​	AZC [=Sheet1!A11]	4934 [=Sheet1!B11]	Shifley [=Sheet1!C11]	Adam [=Sheet1!D11]	48 [=Sheet1!E11]	100% [=Sheet1!F11]
9​	AZC [=Sheet1!A13]	4957 [=Sheet1!B13]	Vegas [=Sheet1!C13]	Tony [=Sheet1!D13]	48 [=Sheet1!E13]	100% [=Sheet1!F13]
10​	AZC [=Sheet1!A14]	43170 [=Sheet1!B14]	Watson [=Sheet1!C14]	Robert [=Sheet1!D14]	38 [=Sheet1!E14]	79% [=Sheet1!F14]

 

[Fluff]

365 Enterprise Edition.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	PROG	ID	Last Name	First Name	Exam	Percentage
2	AZC	52197	Amos	Gladys	42	88%
3	AZC	49810	Azari	Phil	44	92%
4	AZC	43170	Watson	Robert	38	79%
5	AZC	50843	Berry	Linda	40	83%
6	AZC	4957	Vegas	Tony	48	100%
7	AZC	4934	Shifley	Adam	48	100%
8	AZC	8493	Hart	Liz	44	92%
9	BTW	51867	Sabine	Melanie	46	96%
10	AZC	49745	Ivan	Susan	42	88%
11	BTW	50766	Parsons	Jamie	44	92%
12	BTW	15821	Andrews	Stephen	44	92%
13	AZC	4311	Burns	Dave	44	92%
14	BTW	5103	Shine	Ray	42	88%
15
Sheet1

Fluff.xlsm
	A	B	C	D	E	F
1	PROG	ID	Last Name	First Name	Exam	Percentage
2	AZC	52197	Amos	Gladys	42	0.88
3	AZC	49810	Azari	Phil	44	0.92
4	AZC	43170	Watson	Robert	38	0.79
5	AZC	50843	Berry	Linda	40	0.83
6	AZC	4957	Vegas	Tony	48	1
7	AZC	4934	Shifley	Adam	48	1
8	AZC	8493	Hart	Liz	44	0.92
9	AZC	49745	Ivan	Susan	42	0.88
10	AZC	4311	Burns	Dave	44	0.92
11
Sheet2

Cell Formulas
Range	Formula
A2:F10	A2	=FILTER(Sheet1!A2:F300,Sheet1!A2:A300="AZC")
Dynamic array formulas.

 

[barnkat]

Unfortunately, that doesn't work. I've posted a version of the spreadsheet on Dropbox since it needs all three sheets to show the problem. The DATA sheet is unsorted - when I sort, the information on the other two sheets is wrong. The link: College Scores 2024.xlsx

 

[MountainFog]

Can you explain what doesn't work with Fluff's solution?

 

[barnkat]

When I enter the formula given, I get the result "#SPILL!" (without the quotes).

 

[Fluff]

Clear all data from row 2 down & then re-enter the formula in A2 only