Posts by Webbers

    Re: VLookUp Formulae


    Quote from whitey1987

    Hello Webbers.


    That worked brilliantly! Thanks very much I owe you one. :)


    Whit---
    Happy to help! The OZers here have helped me so much, that when I actually have the opportunity to help someone else it is a must. You remember "Pay It Forward", words to live by. I am honored to have the oppportunity to be able to give back to the forum. I have been helped on so many levels here at Oz. Not only with guiding me to the solution, but helping me to understand the WHY aspect when I am working with a function I am not familiar with.

    Re: VLookUp Formulae


    Whitey----


    If I understood you correctly, then this forumula will work:


    Quote

    =VLOOKUP(F15,$L$4:$M$7,2)


    Just paste it into H15 - H35 on your spreadsheet. I tried it and it works. If this is not exactly what you wanted, please clarify. I hope this helps.

    Re: Macros available for ALL documents?


    Just an update..... The macros are working like a charm. It sounds silly, but such a redundant task was tying up time unnecessarily. Now thanks to my friends here at Oz, I can work more efficiently and have more time to not only work on other projects, but also to spend reading the forum here! Thanks to my macros I have found a way to get more time in a day, well sort of. I just wanted to let ya'll know just what a major timesaver having these macros available for ALL workbooks is. I am in awe the great OZ!

    Re: Comparing with key values in 2 workbooks


    Quote

    Maybe it is just me but I found this site to be about the best place to learn.


    I am in the process of trying to learn VBA. I can record macros (the easy way). And I know how to go in and edit them (when it is an easy fix). But I want to learn more, like you guys. I made a "personal goal" to review and read the board here at Ozgrid at least 15 minutes per day, more if my time allows. If I don't have a current "issue" I just review the newests posts. I have found that a great number of people post items that I have an interest in. Reading these posts when I don't have a problem has helped so much, and of course when I do have a problem I have it resolved to 75% correct as opposed to only like 25% like I used to. I know spend my morning coffee breaks and my lunch break at the office reading the forum. As usual, ya'll have helped me so much, even when I haven't asked for your help. There is no place like Oz! :rock:

    Re: Macros available for ALL documents?


    Will----


    That is something I would definately be interested in learning how to do. I just found some great information on ths, so I will make that my "new project" with all this extra free time. Thanks for the info, motivation, and the extra hour of my life for today! :rock:

    Re: Macros available for ALL documents?


    Will----


    I just wanted to let you know that I did what you suggested option 2). It works perfectly. As a result of writing those 6 "regular" macros, I was so efficient today, and had over an extra hour to devote to another new project! I created a tool bar for the macro buttons. I even made some editing to them. This was a major league big help. :thanx:

    Re: Macros available for ALL documents?


    Will----


    Thanks so much for the superfast reply! I think I will go with option 2 for now until I have a chance to work on my VBA coding some more. During my search for an answer prior to creating this port, I saw a post from a user that mentioned that a "repair & reinstall" deleted her back up as it was in the default folder. So I will be sure to avoid that problem. And since the personal.xls you mentioned in option 2 will be a hidden file, it will be perfect for my needs.


    Thanks so much Will! I truly appreciate your time. And the extra time I will save in just one 8 hour day at work will be in excess of 1 hour's time of just sorting! Thanks so much!!!!

    I searched through the forums and could not find anything that met with my current need. I am sure this must have a simple solution, but I am at a loss. I work with zips of Excel files all day. These zips all contain the same specific files, but have different ID #s for the file names. But the hearder names for each worksheet remains constant. Anyway, I wanted to be able to record a macro for sorting the worksheets. This may sound simple, but I spend so much time sorting each worksheet it gets out of hand. Anyway, having the macro attached to the actual file I am currently working in won't work, as I can't access it for the next file, not to mention that each zip has 10 seperate xls files, so I certainly don't want any extra files open. Anyway, is there a way to record a macro that would remain in a "default" document in Excel the way that you can in the "normal.dot" for Microsoft Word?

    Re: Generate data based off validation list


    I just wanted to say Thanks! I encountered a similar challenge. I knew that it could be accomplished, but I was not sure how to get there. As usual, I searched here at Ozgrid, and I found this thread with a similar problem. I was able to easily alter the formula given to meet my needs, and it works exactly as I wanted to. I have said it before, but this time I didn't post a question and ya'll still helped me in a major way! OzGrid and all it's members ROCK! And just FYI, this is the formula that I used in my worksheet:


    Code
    =INDIRECT("A"&SUMPRODUCT((B2:B79=H3)*(C2:C79=I3)*(D2:D79=J3),ROW(A2:A79)))

    Re: IF Statement with multiple criteria


    That works perfectly! Thanks so much! I am still working on fine tuning my ability to combine multiple functions. I appreciate your help so much.


    No excuse? Possibly, but I did confess that I am a junkie. Hi My name is Sherry and I am an Excel-o-holic! So as a result when it is almost 1am, what else would I do but play in Excel!


    I can always get help on this forum and by such outstanding people. Now I can go to sleep without this fomula beating me up! Thanks again! :thanx:

    Re: IF Statement with multiple criteria


    First of all, thanks for replying so quickly! It is NEVER too late to be playing in Excel! I have become an addict (Excel Junkie). I have always been one of the most knowledgable people in my offices when it comes to Excel. But compare to ya'll, not even close!


    I tried that formula, but it resulted in a #VALUE! error. The error occured in all cells (in column G where the formula is).
    I also tried:


    =IF(OR(ISBLANK($C6),$C6=""),"",IF(OR(ISBLANK($E6),$E6="0"),"",$C6*$E6))


    and that seems to be closer, but I get the #VALUE! error if Column E is blank. So I am making progress. Currently that is the only setback. Can we alter that formula to aacount for Column E possibly being blank?

    I am working on a formula, and it just is not adequate. The formula is designed to determine the total minimium price. I do not want to see any type of error if any of the cells are blank of course. And it is possible that columns C, D & E will be blank, or any combination of blank cells. What I have right now is:


    =IF(OR(ISBLANK($C6),$C6=""),"",MIN(IF($D6:$E6>0,$D6:$E6)*$C6))


    Column C is Quantity, and is just a number, but can be blank
    Column D is Regular Price, and is a dollar amount, but can also be blank
    Column E is Discounted Price, and is a dollar amount, but can also be blank


    Basically in a nutshell, I need to determine which is the lower price either column C or D (excluding 0 or blanks). The result (lower price) would then need to be multiplied by the quantity to get the final result. Normally column E would be the lower price, but I have learned never to assume anything. So if Column E is blank, Coulmn D would actually be the lower price, and they could also be the same.


    Thanks in advance. I have been working on this one without success for far to long. Thanks again!

    Re: SUMIF Less than or Equal to a date


    Now this formula works! It didn't work until after I rebooted my system! But at least it does work. And since I was so proud of myself, I added a 2nd formula in the cell below this one that calculates for a range of dates:


    =SUMPRODUCT(($E$7:$E$844>=$A$1)*($E$7:$E$844<=$A$2)*($B$7:$B$844))


    I am glad I was able to solve this one on my own. But that is only because I spend so much time reading everyone elses posts! Thanks again everyone. I figured I would post my additional formula on the off chance it can be helpful to someone else.

    Okay, I am trying to revise a formula, and I am sure I am somewhat close, but, I just cannot get this to work properly. The formula I have right now is:


    =SUMIF($E$7:$E$844,"<="&$A$1,$B$7:$B$844)


    Column E is a list of quantities, in number format.
    Column B is a list of dates, in date format (mm/dd/yy)
    Cell $A$1 contains a date I want to reference. This date may be occassionally changed manually, which is why I am referencing a specific cell within my formula.


    Based on the dates listed in Column E that meet the criteria of being less than or equal to the date listed in $A$1 I need to total the quantities listed in Column B.


    Thanks in advance for all your help!

    Re: PivotTable macro constantly changing sizes


    Andy----


    Outstanding!!!! It works perfectly! I added it to the Excel spreadsheet, and then tried it. Then I removed some data from the "data" worksheet and refreshed the pivottable and used the macro again. Then I did it again. It works exactly as I wanted it to!!! Thanks so very much for all your help! Because of you, and everyone else at OzGrid, there has never been a reason to go to another Excel forum!!!

    Re: PivotTable macro constantly changing sizes


    Andy---


    Yes, that works! But I have another question. Can I incorporate that script into my current script? I used that as is, and it works, but when I attenpted to intergrate it into the existing one, I get errors.

    Re: PivotTable macro constantly changing sizes


    Andy---


    The data is on one sheet and the PivotTable report is on another. The worksheet that the data is listed on is "data" and the worksheet that the PivotTable is on is called "report", does that help? I really appreciate your help!!!

    Okay, I am stumped on this one. And I know if there is an answer, THIS is where I will find it. First of all, I know almost nothing about VBA. I recorded a macro and I am having to constantly edit it as the number of rows in my pivottable is constantly changing as information is added and removed.


    This macro was written to format the PivotTable and copy it, so I can easily paste it into FrontPage. So I do it all in one click. Well, currently when the data changes, I go into the macro and manually edit the number of rows listed in the formula. I would like to avoid having to do this, and I am sure there is a way that Excel can do what I want, I just don't know what it is. below is the VBA code for my macro:



    Thanks in advance for all your help!!!

    Re: MIN excluding 0 values AND BLANK cells



    Dave----


    The Array formula works perfectly! Thanks so very much! I have looked at the Excel Help file for DMIN and I do not see why it would be more efficient to do it that way. I appreciate all your help.

    Re: MIN excluding 0 values AND BLANK cells



    I don't understand the formula you used in the example workbook. But I have learned to always try the advice on this forum. I used the following formula:


    =IF(ISBLANK($E390),"",IF(COUNTIF($Y390:$AC390,0)>0,SMALL($Y390:$AC390,COUNTIF($Y390:$AC390,0)+1),MIN($Y390:$AC390)))


    which incorporates the initial ISBLANK forumula along with the formula you provided. It does work, and I thank you for it, although, when I copied it into the other rows, I did encounter a slight problem. there are a few rows that in columns Y-AC have all blanks, with one or two 0 values. As a result of the MINIMUM that excludes 0 (which is what I want), the formula shows an error of #NUM! Is it possible to alter this current formula, so that if this happens, then the formula would result in a blank cell rather than the #NUM! I have no clue on this one, because I don't understand the current formula. I have never used the SMALL function, and I would have never though of using the COUNTIF function to get this result, since technically I am not counting anything. Any help you can offer would be appreciated. Thanks so much!