Posts by txheart

    Hi y'all,
    I've searched over 4 different Excel help forums trying to find a way to do exactly what I want, but I am just not finding it. Could be I'm blind, but I'm just not finding it. So, I'm gonna have to post after all.


    I run a log of television & appliance returns. That's all it is, just a log of all the info. Because it has all the info that I need for the returns (serial, customer, when/if the return was filed, etc.) it is vitally important to my job. I've had to start from scratch once, and I don't ever want to have to do that again! The one thing that I've done a few times is change something & save it, and somehow screwed up everything else - then I can't remember what was what & where. I make changes to this log daily, sometimes several times a day.


    I want the log, titled RA Sheets & Log, saved in C:/Backup/Log Backup-mm/dd immediately upon open. That way, any changes I, or anyone else, make can be easily undone. Ideally, I'd be able to actually have 2 backup folders, one that's the every time it's opened backup & the other for an automated monthly backup. I would want the last 7 backups in the folder, anything older could be deleted. In the monthly backup I'd want one every month, like on the 1st or whatever.


    Any help y'all could give me is very much appreciated. I am a VERY inexperienced VBA & macro user. Since I'm almost 100% certain this solution will consist of either VBA or macros, or both, I better let you know to please tell me exactly where to put stuff. Telling me to insert a module, for example, is just gonna confuse me. lol


    Again, anything that y'all can do to help is appreciated. Please let me know if you have any questions. Thanks!


    Ky

    Mornin' y'all, I've got a big project that I'd like to do, but I'm stuck at the very beginning. I've posted this question in a few other forums over the last week, but I've received no answers, so am hoping y'all might be able to help out. I'm basically doing an auto-ish grocery list with coupons.



    I've got one workbook, tab A=Master List, tab B=Shopping List, tab C=Coupons. On Master list is a page full of categories/sub-cats (beans/grn bns, cereal/Cheerios, etc.) with a checkbox next to it. (A,B,C=checkbox, item, linked cell). Shopping list is blank except for the headings & outline.



    Coupons is running list of all of the coupons we have available. Ultimately, my goal is to have Shopping List insert items as follows: When I click a checkbox on Master List, it should automatically insert the item sub-category (Cheerios) into the item column on Shopping List. Once an item is listed on the Shopping List, then I'd like it to scan Coupons for the sub-cat (Cheerios); if a coupon is found for that sub-cat, I'd like the info to be copy/pasted into Shopping List. If there is no coupon, my Shopping List would just show Cheerios, but if there is a coupon then my Shopping List would show Cheerios, and the coupon info ($.50 off 1). I've got the basic outline of my spreadsheet on my Google Docs page at https://spreadsheets.google.co…CaU1TaUw2a3g1SGc&hl=en_US if you like to see it. I'm having trouble with: 1) getting the checkbox (onclick?) to copy the sub-cat to Shopping List and 2) having Shopping List scan Coupons for the sub-cat name and paste the info found into Shopping List. I might could figure the coupon part out if I tried long enough, but I don't have a clue how to do the checkbox one. Help? Thank you for taking the time to read this, I know I'm long-winded ... ~Ky Wow, I can't seem to make carriage returns stick, this is gonna be hard to read ...

    Re: Update worksheet from Master workbook


    Uhm... how? Sorry, I've searched using the Excel Help and keywords "properties" as well as "timed update" but I'm not getting an answer. I'm not using a pivot, should I be? LOL, I figured I'd just hit the exclamation points every morning. That's still a LOT faster than what I was doing up to now. =)

    Hello all,
    I've searched through the forums here, and some of the answers come close, others assume I know the prerequisite's. Unfortunately, I'm basically an Excel Dummy. I thought I was intermediate, even advanced, until I started coming here!


    There are 7 files on the network that everyone updates. These are contact databases for clients, insurance companies, our agents, etc. Any changes that need to be made are made. The first person to open a file will get edit mode, the others will only get read-only mode. I rarely have to edit, so if I'm first then I'll invariably be asked to exit so others can update them. I've tried doing the share workbook, but it's not working. That's not what I'd like to do anyway.


    I've made a workbook with 7 different sheets, each tab named after one of the network databases. I'd like for these sheets to automatically update if/when changes are made to the master - but keep my formatting intact. Right now I'm relying on import external data, but that doesn't update. Therefore, I have to delete and re-import every morning, losing all of my formatting. Surely Excel has an easier way to do this?


    The databases are highly sensitive information so I can't really attach one - but I will attach a dummy example of what they all look like. Each file is hundreds of rows and maybe 15-20 columns, so the thought of linking each cell is more than I'm looking to do. I do not know anything about VB, but I'm willing to learn... Help?
    ~Ky

    Re: If And Sum Formulas In Same Cell



    Uhm, I did use Sicarii's suggestion. It's in my worksheet right now, but I still needed more and didn't know how to put the two together. Which is what all of this is doing. Sicarii's suggestion was absolutely perfect for the part that it answered.


    I just plugged your suggested formula into my cell and hit enter... wooohoo! Thank you so much Eric, it's perfect! Now to study it so that I can figure out what you did...

    Re: If And Sum Formulas In Same Cell


    Insured's monthly premium is 234.25, multiplied by 12 to get the yearly premium = 2811
    subtract 25% up to a max of 500 = 2311
    multiply by the number of years left in life up to avg lifespan of male, 20 = 46220
    (D20*12) = 2811
    (D20*12-25%up to 500) = 2311
    (D20*12-25%up to 500)*(79-D22) = 46220


    Insured's monthly premium is 234.25, multiplied by 12 to get the yearly premium = 2811
    subtract 25% up to a max of 500 - 2311
    multiply by the number of years left in life up to avg lifespan of female, 24 = 55464
    (F20*12) = 2811
    (F20*12-25%up to 500) = 2311
    (F20*12-25%up to 500)*(79-F22) = 46220


    Add his total to her total for grand total. My goodness, I sure hope that helps. Heh, I'm confusing myself now.

    Re: If And Sum Formulas In Same Cell


    Thanks so much for your reply, Eric. Unfortunately that formula did not give me the answer I need. Let me try to explain better what it is that we are trying to do...


    234.25 * 12 = 2811.00 * 25% (the result must not be greater than 500) = 2311 * 20 = 46220.00


    D20 * 12 = D32 * 25% (this number must not be greater than 500) = 2311 * (79-59) = final answer I need


    Does that make it clearer? The 500 maximum is the times 25%. In this example 234.25*12*25% is more than 500, therefore 500 must be used. I used the formula you gave me but am not getting the correct totals. I sure wish I was better at these formulas than this, but I certainly see the need to get that way!


    Thank you for help and I sure wish it had worked.


    Yesterday's request for help can be found here

    Good morning again,


    I got help here yesterday and thought that took care of the worksheet that the boss wanted but I was wrong. He wants more formulas and I'm still stuck.


    The MS State Tax Credit maximum per person, per year is $500.00. I need to multiply the monthly credit allowance by 12, then multiply that by 25%. This answer must not exceed $500.00. I need this formula for both the male and female. I can't figure out how to make the formula both a SUM and an IF.


    EX:


    (79-D22)*(D20*12*25%max $500)+(83-F22)*(F20*12*25% max $500)


    Any chance someone could help me with this one?

    Good morning again,


    Boss needs me to formulate a worksheet and I dont quite know how to tell it what it needs to do. Let me see if I can explain. The MS State Tax Credit maximum is $500 per person. I need my worksheet to give me a total of his and hers credit taken but it cannot exceed $500 each. The formula is nested inside another formula so that might make it change the way it's written a little bit.
    Ex:
    (D21*12)*F23+(E21*12)*G23 with the paranthesized (is that even a word?) sections being what I need to cap at $500. Can anyone help me with this please?

    I just found this website because I'm stumped. I did a search myself, then went through the 5 pages the "Post" function brought up. I'm sure it's been asked before, I must just not be using the right words


    I need to tell every cell in one column to multiply itself by a firm number and a percentage.


    Ex: Column F will be the result of $77.80 times 75 percent all the way down the column. Column G will be $27.21 times 100 percent all the way down.


    I could enter the formula into every cell, but I wondered if there's a way to just tell the whole column, only once, what it needs to do. Thank you so much for your help!


    PS-I work for an insurance company. The employees of a company want a spreadsheet showing exactly who pays exactly what. Ex: Their paystub shows $142.90 coming out of their paycheck. Their employer pays 100% of medical, 75% of dental. The columns would show Total, Employer Medical, Employer Dental, Employee Medical, Employee Dental. I hope that makes sense. =/