Populate a range with formulas based on value in a cell

[mystiedx]

Hello to all!

I have 2 sheets, called A and B.

In Sheet B, I have a cell, F2, that contains an IF formula. This formula extracts a value from sheet A.

In sheet B, I also have a range of cells that needs to be populated with formulas based on the resulting value in F2.

I've written the VBA code to auto fill the range with formulas. However, I don't know where to place the code. I've tried to put the code in Worksheet's Change, SelectionChange, Activate methods but nothing works.

Any help will be most welcome.

Thank you.

 

[Radoslaw Poprawski]

please provide minisheet.
this should be resolved with no vba.

 

[mystiedx]

please provide minisheet.
this should be resolved with no vba.

I actually found where the problem is. But I'm sending you all the same. I would love to know how to do this without VBA.

mini sheet.xlsm
	A	B	C	D	E	F
2	Grade	10 Op A			Subject:	Computer Science
3	Roll	7
4			P1	P2		Exam Mark
5	Serial no	Name				100
6	1	Jim	10	23		33
7	2	Jack				0
8	3	Jill				0
9	4	Joe				0
10	5	Anne				0
11	6	Sophie				0
12	7	Millie				0
13	8
14	9
10 Op A

Cell Formulas
Range	Formula
F2	F2	=IF(OR($A$91="10",$A$91="11"),INFO!$B$10,IF(OR($A$91="12",$A$91="13"),INFO!$B$11,INFO!$B$9))
B2	B2	=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
B3	B3	=COUNTA(B6:B45)
F6:F14	F6	=IF(ISBLANK($B6),"",ROUND(C6+D6,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:E45	Cell Value	>79.5	text	NO
C6:E45	Cell Value	between 34.5 and 40.4	text	NO
C6:E45	Cell Value	<34.5	text	NO
F6:F45	Cell Value	between 39.5 and 49.4	text	NO
F6:F45	Cell Value	<39.5	text	NO

 

[Radoslaw Poprawski]

alright, but why use if?
why dont you use vlookup or hlookup or xlookup to search for values with criteria?

you did not provide the second sheet.

 

[mystiedx]

alright, but why use if?
why dont you use vlookup or hlookup or xlookup to search for values with criteria?

you did not provide the second sheet.

mini sheet.xlsm
	B	C	D	E
5	Computer
INFO

There's a formula missing in the first sheet. It's in cell A91. I've used LEFT function to extract the grade from the value in B2. That's why I have not used Lookup. The value in F2 depends on the value in B2.

 

[mystiedx]

mini sheet.xlsm
	B	C	D	E
5	Computer
INFO

There's a formula missing in the first sheet. It's in cell A91. I've used LEFT function to extract the grade from the value in B2. That's why I have not used Lookup. The value in F2 depends on the value in B2.

mini sheet.xlsm
	M
21
INFO

 

[mystiedx]

mini sheet.xlsm
	M
21
INFO

mini sheet.xlsm
	A	B	C	D	E	F	G	H
1	Fill in following information:
2
3	School
4	Teacher
5	Department	Computer
6	Year	2121-2022
7
8	Subject Names
9	Grades 7-9	ICT
10	Grades 10-11	Computer Science
11	Grades 12-13	Computer Science
12
INFO

 

[Radoslaw Poprawski]

you can use vlookups with left on the array or lookuped values.

Also,can you prepare those tables as they should be?
I don't see correlation between sheet from post1 and post 7

also describe in details what you want to achieve here from the start, since no1 here exept you is familiar with those files

 

[mystiedx]

you can use vlookups with left on the array or lookuped values.

Also,can you prepare those tables as they should be?
I don't see correlation between sheet from post1 and post 7

also describe in details what you want to achieve here from the start, since no1 here exept you is familiar with those files

OK. Let me explain.

This is a model mark book to be used by teachers. On the first sheet (INFO - Sheet A), they will fill in their details.

Then there will be one sheet for each class the teacher works with, ranging from grade 7 to 13 (Sheet B is one example of such a sheet). Not all teachers work with all grades. The workbook will calculate the final mark of students based on the different papers they sat for.

Now, for grade 10 onward, the number of papers differ from subject to subject, and the formula used to calculate the final mark is also different.

What I've done:
In sheet INFO, the teacher will enter the name of the subjects for the different grade levels (7-9, 10-11, 12-13) as they may be different.
On the other sheets, I've used LEFT to extract the grade from the name of the class, then used this in an IF formula to populate the subject field (F2). Note that the subject will be different for each teacher.
I've used VBA to populate the Exam Mark column with the appropriate formula according to the subject in F2. I've kept the formulas relative to make the mark book flexible.

I had posted initially because I had thought my code wasn't working. But then I found my mistake (a stupid one ?).

But then you said the populate formula part could be done without VBA. And I'd like to learn how.

 

[Radoslaw Poprawski]

Alright,
There are couple of ways this can be done, but:
Firstly b4 I even start, is this the final layout or can't be changed? (To proper columnar database layout, which is better to work on.)

Book2
	A	B	C	D	E	F	G	H
1	Fill in following information:
2
3	School
4	Teacher
5	Department	Computer
6	Year	2121-2022
7
8	Subject Names
9	Grades 7-9	ICT
10	Grades 10-11	Computer Science
11	Grades 12-13	Computer Science
12
Sheet1

Also, grades 7- 9 have an ict subject while I do not see it on the main file from post1.
next thing is u can use indirect to grab values from different sheets or tables (its better to use tables ctrl+t)

and then its easy to find a way to either filter data or use some lookup function with criteria to look for specific value.

Here is an example of using indirect:

Be aware: this was b4 Tables were introduced, but in the same manner we can create Table nomenclature to retrieve values