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.

 

[mystiedx]

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

Is this final layout? Yes. But there are other things on this worksheet which I've not included here because they are not relevant to the discussion at hand.

You don't see ICT because it is for Grades 7-9. And the formula issue doesn't apply to grades 7-9.

The whole mark book is actually way more complex than what's shown here. In fact I've been using it for some years. This year new things cropped up and I had to update the mark book with these new things. That's when I got the issue with the code not working.

I don't know if tables existed back in Excel 2010 (that's the version I used to build the mark book), but I don't really know how to use them.

I believe to use tables, I'll have to start the system from scratch?

 

[Fluff]

If you have got your code to work, I would stick with that.
Also I'm not a particular fan of structured tables & so would suggest you keep your sheets as they are now.

 

[mystiedx]

If you have got your code to work, I would stick with that.
Also I'm not a particular fan of structured tables & so would suggest you keep your sheets as they are now.

I'll do just that.

Thank you Fluff and Radoslaw for all your suggestions. Much appreciated.

 

[Fluff]

Glad to help & thanks for the feedback.