+ Reply to Thread
Results 1 to 9 of 9

Drop down, highlight, and calculations on a sheet.

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    30

    Drop down, highlight, and calculations on a sheet.

    Hi All,

    Been a long time. Not sure if this is the right forum / section for this, but I'll attach my sample sheet and will try to post what I'm looking to do or if it's even viable with Excel.

    The file is just for quick glance of pay rates at my company etc. I want to be able to select from the Drop Downs at the top (highlighted yellow - minus the HRS) the "Years with Company (yrs), Equipment type (Equip), Position (A or B), and Months (Mths: 1 - 12) and then have the cell(s) highlighted it corresponds to:

    For example selecting: 06, AC 7, B, and 01

    It would highlight in the first (green) table year 6 and AC 7 rate (in this case $6.00). I would like to see it highlight light yellow down and across and light red highlight the pinpointed cell.

    I would also like it to calculate every cell / rate from 1 hour (which would be the default) to whatever number of hours is selected, plus also be able to multiply it by months selected (something like =rate*($I$1*24)*$k$1. I don't know if you need to have ghost cells with the default pay rates and have it reference them for the calculation (or the rate base to start the calculation...or if it can be done with the data in the cell already). In the end, basically, a person would be able to select the drop downs and pin point the plane, seat, and the rate for that year. They'd also be able to type hours of pay and see what they'd make, while also being able to see what they could make per 1 month all the way to 12.

    Probably a lotta bit ambitious for my skill level, but really looking forward to suggestions and tips! Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Drop down, highlight, and calculations on a sheet.

    As for highlights:
    highlight column:
    Please Login or Register  to view this content.
    highlight row:
    Please Login or Register  to view this content.
    highlight intersecting cell:
    Please Login or Register  to view this content.
    I don't understand your calculation req's...
    Attached Files Attached Files
    Last edited by protonLeah; 06-30-2020 at 12:53 AM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    05-08-2013
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Drop down, highlight, and calculations on a sheet.

    Fantastic Ben! I will incorporate this for the highlighting. You're a huge help!

    My knowledge on Excel is pretty basic. Where do these formulas get entered? On some conditional formatting or somewhere else?

    EDIT: Nevermind, I see where it's in the conditional! Fantastic, love it! Thanks again!
    Last edited by FlyinIron406; 06-30-2020 at 01:54 AM.

  4. #4
    Registered User
    Join Date
    05-08-2013
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Drop down, highlight, and calculations on a sheet.

    Quote Originally Posted by protonLeah View Post
    As for highlights:
    highlight column:
    Please Login or Register  to view this content.
    highlight row:
    Please Login or Register  to view this content.
    highlight intersecting cell:
    Please Login or Register  to view this content.
    I don't understand your calculation req's...
    Again, this is Awesome! Thank you so much. One question though, would it be possible to have the selected box (the red outline) to Fill a different color between the top table and bottom table?



    Totally missed your misunderstanding the calculations.



    Basically, I want the table to be able to display hourly pay (which it does in current fashion), but then be able to type in hours and have it calculate that. It'd be like a monthly total. So:

    Pay Rate in Cell * HRS ($I$1 * 24)
    Then I'd also have it do monthly to yearly (which would be if they selected 12 on K1).

    Basically I'd like to have it something like this:

    Cell Rate * ($I$1 * 24) * $K$1
    I know how to do like a ghost table that has all the fixed hourly rates and be able to do the calcs from that which would allow a copy / paste on all cells to achieve what I wanted.

    Would be something similar to (assuming cell B4)

    =B31*($I$1 * 24) * $K$1
    Each cell would then refer to a ghost / hidden list etc.

    So ultimately my question is, is there a way to just use this current table / data, and have a cell refer to itself or use the data in that cell to do it to where I can mass copy / paste, or would I have to manually do every cell to have that amount for the "cell rate" above. In that case, the ghost cells would be better.

    Thanks again!
    Last edited by FlyinIron406; 06-30-2020 at 07:59 PM.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Drop down, highlight, and calculations on a sheet.

    To have the cell filled with different colors requires two equations instead of the one I gave you.
    Top (B):
    Please Login or Register  to view this content.
    Bottom (A):
    Please Login or Register  to view this content.
    It seems that you want to use the cell at the intersection of the year and pos to use in your calculation. If so then try:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-08-2013
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Drop down, highlight, and calculations on a sheet.

    That’s so perfect! Thanks so much!!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Drop down, highlight, and calculations on a sheet.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    05-08-2013
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    30
    Quote Originally Posted by AliGW View Post
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Sorry it took so long, was out of town. Thanks. Will do!

  9. #9
    Registered User
    Join Date
    05-08-2013
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Drop down, highlight, and calculations on a sheet.

    Hi All,

    Just wanted to open this again one more time! I was wondering if there'd be a way to make the yellow highlight go all the way across the table. Not just to the special highlight cell, but to the end of the table as well. That way it'd show the rate a person would be at in all airplanes.

    Thanks so much~!

+ 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] Drop List and Calculations
    By Dallas6570 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2020, 11:08 AM
  2. calculations through drop down list in excel
    By asood2708 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2018, 03:22 AM
  3. Calculations using multiple drop down menus
    By snot369 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2017, 12:51 PM
  4. Highlight data rows not matching calculations
    By drgkt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2016, 03:54 PM
  5. Drop Box automated calculations
    By ahh1214 in forum Excel General
    Replies: 3
    Last Post: 11-07-2015, 04:57 PM
  6. Linking drop down menus to calculations
    By magicmark in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2012, 12:18 AM
  7. Drop-downs + calculations
    By jeanva in forum Excel General
    Replies: 0
    Last Post: 02-21-2007, 12:27 PM

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