Closed Thread
Results 1 to 20 of 20

Populate a cell based on conditions of other cells w/out putting a formula in the cel

  1. #1
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Populate a cell based on conditions of other cells w/out putting a formula in the cel

    Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
    Last edited by Dustinsmith; 10-14-2009 at 04:42 PM. Reason: I was asked to

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is there a way??

    Welcome to the forum.

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Is there a way??

    please, rename your thread according to the rules

  4. #4
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Umm ok I think I have stumped you. So am I to vague? Is it possible?

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Hi,

    it would have to be with VBA. Are you comfortable with that?

  6. #6
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Well I have never used VBA but I am at a point with Ecell that I want to learn more. Is there a place I should go to learn more, do you have any sugestions? So basically I can be comfortable with that.

  7. #7
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Ok so I tried to Learn Some VBA and so far I got this ...
    Please Login or Register  to view this content.
    and it doesn't work any help?
    Last edited by Dustinsmith; 10-13-2009 at 12:50 PM. Reason: I was warned

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Dustin, code tags, please.

  9. #9
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
    Have you considered using Data Validation to force users to enter or select the correct inputs? Possibly, you could combine Data Validation with dependent lists. This may be a better choice than VBA, but your posts lack sufficient details to say for certain.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  10. #10
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    You could definitely use a macro to autopopulate a cell with a variable default, depending on precedents - what we would need to do is create a macro which fires when the last cell before your key cell is populated - assuming that all other precedents were populated, we could then perform a calculation in the VBA to spit a value out onto your key cell.

    This would:
    Break Excel's undo functionality
    Be unpredictable if your precedents weren't right
    Require an uploaded example, showing the precedents (highlighting "last to be populated"), the output target and the logic of the precedent combinations and the equivalent output

    CC

  11. #11
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Data Validation won’t work in this case.
    The reason I am trying this is because they forget to input the data because they go hand in hand If the employee's craft is Operator then they get Truck Rent, or if they are the Welder Foreman they get Weld Rig pay, but this information must be there when it gets to payroll because they don't care if it is standard, if the information isn't there then they don't pay it.
    There are a few more standard upgrades but in the same column there could be other upgrades for different crafts. Depending on the day the crew and the upgrades could be different. So it is impossible to write an IF formula in the cells because that wouldn't allow them to type the other not so standard upgrades.
    I like the VBA option I just need to do some more reading. I was hoping to get some help making it work for now while I learn a little more. The other option is to reformat the form to add an additional column only for rig and truck upgrades but that would be redundant and hard to read.
    I suppose it isn't easy to understand when you haven't seen the form, but there is a lot of data in about as small of an area as possible. I don't think I can cram one more bit of information in it.
    If anyone wants a copy of the form to have a crack at improving it let me know I will be happy to give you a shot.

    Thank you for the suggestion though I appreciate it.

  12. #12
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    I don't know if I like the idea of breaking the undo function? What do you mean by unpredictable? ..... I will uploading as soon as I figure out how and where.
    Last edited by Dustinsmith; 10-13-2009 at 02:18 PM. Reason: spelling

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    When posting a reply (not a quick reply) scroll down to "manage attachments." Use that to attach your file. If it's very large, zip it first.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  14. #14
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Ok I removed all of the personnel information such as names but those aren't important, I also removed the heading information to keep myself out of trouble, however most of the boxes in the heading normally contain critical info for accounting. The yellow highlighted area is where I would like to have the upgrade information if the green highlighted area meets certain criteria:

    If the green area is "Operator" the yellow should be "Truck Rent"
    If the green area is "Welder Foreman", "Welder Steward", or "Lead Welder" the yellow area should be "Weld Rig".

    Of course any other improvements wouldn't hurt my feelings.
    Thank you for looking at this!
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Hi Dustin,

    Although we prefer people to read the rules first... it's uncommon for someone to get picked up on the rules twice and not be a <insert appropriate expletive> - fair play.

    This code needs to be inserted in the workbook code:
    Alt+F11
    There's a file-explorer-type view on the left - identify your workbook (in bold) within this, there are several sheets ((Sheet1) Monday, (Sheet 2) Tuesday ..etc) underneath is the workbook (ThisWorkbook) - double click it
    paste code in there
    Please Login or Register  to view this content.
    Now test it.

    Undo function is gone - this is an almost unavoidable manifestation of sheet-change macros (and avoiding it is such a monumental catastrophe of an endeavour I'm not even going to imagine you will ask me to explain it)

    You can ignore the "unpredictable" comment - the situation is v. simple so it won't be a problem.

    You mentioned you wanted to learn more so I have commented the code fairly heavily (very heavily for me...)

    CC

  16. #16
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    OK I am a (Incert Expletive) because I can't seem to get it to work. I hit alt f11 double clicked this workbook and pasted it into the dialog box when I go back to the workbook it isn't in the list of macros to use. So I tried to create one using the name you put at the top then patsing it into the box minus the name and it will try to run it but gives me errors. am I doing this wrong?

  17. #17
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Um... yes

    If you look at the top of the code, you'll see it says:
    Please Login or Register  to view this content.
    Amongst other things, the private means it won't show up in your list
    More to the point, Workbook_SheetChange is a special kind of macro, an on event macro, which fires automatically when something happens - in this case, when something changes on the sheet - if you read your first post, that's exactly what you asked for!

    So...

    I'd start with a clean version (not sure what you may have changed in the VBA) - follow the instructions again (you seemed to get that part fine). Then, where I said "Now test it." I meant, go to any of your "Work day" sheets, and type one of the job roles into a cell in A8:A30.

    CC

  18. #18
    Registered User
    Join Date
    10-10-2009
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Woot-
    It works. Sorry I didn't get why it wasn't showing up, thank you for the education, You ROCK. This will save me some minor headaches in the future. Wow for something so simple you think it wouldn't take such a complicated effort to make sure these guys get paid right. Thank you again.

  19. #19
    Registered User
    Join Date
    04-12-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the

    Hi, I used this code for another application and am wanting to make a couple of changes to apply to my workbook.... Instead of "Truck Rent" or "Weld Rig", I want it to pull the the data verbatim from another cell in the same row. Is this possible?

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,729

    Re: Populate a cell based on conditions of other cells w/out putting a formula in the cel

    Welcome to the forum!

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed Thread

Thread Information

Users Browsing this Thread

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

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