+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    18

    Question 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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    18

    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. #4
    Registered User
    Join Date
    10-11-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    18

    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. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

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

    @ jamietofs

    Welcome to the forum.

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    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. #6
    Registered User
    Join Date
    10-11-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    18

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1