+ Reply to Thread
Results 1 to 5 of 5

Use a formula based on a value in another cell

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    NC,US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Use a formula based on a value in another cell

    I'm relatively new to excel and have no experience with vba and macros, so any help is greatly appreciated!

    I am working on a spreadsheet to track physical fitness tests.

    I need the formula in the Pass/Fail column to auto-populate based on the age I have entered. Each formula is unique to each age group (20-29, 30-39, 40-49, 50-59) and what their qualifying times are.

    Are there any macros that may be able to help me?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,385

    Re: Use a formula based on a value in another cell

    What are the criteria for each age range to Pass?

    Edit: OOPS! I see them in your formulae.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,385

    Re: Use a formula based on a value in another cell

    No VBA necessary. Used Vlookups. Look at the attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-04-2013
    Location
    NC,US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Use a formula based on a value in another cell

    That's perfect, thank you.

    Couple more questions:

    -Is there a way to fill the cells red/green in the formula based on passing/failing scores without using the conditional formatting?
    -Can the gender be factored into this formula where females have different requirements?

    Thanks!

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,385

    Re: Use a formula based on a value in another cell

    Coloring of the cells would require conditional formatting. Because requirements for gender will vary, I suspect that you will need to replicate the look up table I provided and use a similar formula, but on a separate worksheet. Vlookup can only support one value being looked up. However, having said that, I can think of a work around. Suppose you used the age for men as 120, 130, 140, 150 and for women you used 220, 230, 240, 250. Then you could do it in one lookup table and one spreadsheet of data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to fill a cell based on drop down list in previous cell
    By formatting freak in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2013, 01:18 AM
  2. Replies: 2
    Last Post: 02-01-2013, 01:57 PM
  3. Replies: 2
    Last Post: 06-22-2012, 08:51 AM
  4. [SOLVED] Formula question-change formula based cell calling it?
    By seethesun in forum Excel General
    Replies: 2
    Last Post: 02-08-2009, 01:39 PM
  5. [SOLVED] Cell Formula reference to cell Based On third Cell Content
    By Gabriel in forum Excel General
    Replies: 0
    Last Post: 02-11-2005, 02:06 AM

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