# Creating a formula to populate data in particular cells based on conditions in other cells

1. ## Creating a formula to populate data in particular cells based on conditions in other cells

Hi there,

I had another issue with this spreadsheet resolved a few days ago. Thank you everyone for their help.

This moves me onto my next problem however, which is setting up fairly complex formulas. I’d like to set the following formulas up in the first tab:

Firstly, depending on what is selected in column D in the first tab (tv or radio) I’d like to make the cell in column E or F for the same row be shaded out grey. So say TV is selected in cell D2, I obviously won’t need to select a radio supplier, so then cell F2 is shaded out, etc.

The second is more complicated, and I should note more important than the first, so if only one formula is possible I’d certainly take this one. I’d like the selection in column L (selection can be anything from 1 to 5) to contribute to an average in the data tables in the “Data” tab. The problem is, I want Column L to contribute to an average in particularly cells, depending on what is selected in columns A, and E or F in the first tab. Let me provide an example:

Say column A (region) is Brisbane, and in column F, we select the radio station as Nova. Once the campaign finishes the region gives its perceived success of the campaign as 4 out of 5. We’d then put 4 in column M. I’d then want this to contribute to the average in cells C5 (as its Brisbane radio), and F27 (as its Nova in Brisbane) in the “Data” tab.

I realise it would be quite labour intensive to set this up for all cells in the data tables but if someone could at least show me how to go about it in relatively simple terms that’d be absolutely grand. I have intermediate level knowledge of excel and this stuff is way out of my depth.

Thank you kindly for any help offered.

Regards,

Jamie.

2. ## Re: Creating a formula to populate data in particular cells based on conditions in other c

Hello
Have you considered creating an Excel Table on your 'Media Summary Sheet' tab and then referencing that on the 'Data' tab, using AVERAGEIFS functions? Take a look at my attached file to see if it's something that could work. Using a Table your data is dynamic, as you add more records the functions automatically update. As a suggestion, I've also added a number of Named ranges to create a series of dependent drop down lists for the TV and Radio stations for each Region.

Hope it's of some value.
DBY

3. ## Re: Creating a formula to populate data in particular cells based on conditions in other c

Thanks DBY! That's excellent.

I'll look at this today and get back to you.

4. ## Re: Creating a formula to populate data in particular cells based on conditions in other c

Just looked into the sheet you sent back in a little more detail DBY. Absolutely fantastic. You've solved all the problems that I had and even made my formatting look infinitely better!

Thank you so much!

5. ## Re: Creating a formula to populate data in particular cells based on conditions in other c

@ jamietofs

Welcome to the forum.

Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

6. ## Re: Creating a formula to populate data in particular cells based on conditions in other c

Thanks Cutter. Forgot to click the solve button but I did the reputation star already.

Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)