Posts by Paul51480

    Re: SQL multiple counts from same column


    I have something that will work nicely for me;

    Re: SQL multiple counts from same column


    Hi all,
    I have made a bit of progress. using UNION I can pull the counts I want but they all are under one column with the label of the first query. Here is what I have now;


    I get counts I can use but instead of 1 column for each count with the column labeled, I get 1 column labeled 2B that has a seperate row for every count. While I can make do with it the way it is, I would love to have it display in seperate columns.

    Hello everyone,
    I am just getting into SQL and I am having a rough time figuring this one out. I am trying to pull a distinct count of a column based on the first two charecters. here is what I have so far;

    SQL
    SELECT count (distinct loc.externalname) "2B"
    from ant.wmsstockitem si
    join ant.wmsloadunit lu on lu.id = si.loadunit_id
    join ant.wmslocation loc on loc.id = lu.location_id
    where (si.ss_state) = 'WIP_DCC' and loc.externalname LIKE '2B%' AND (si.ss_reasontext) !='Missing best before date'


    This returns one row and one column with a count of every unique location that starts with 2B. I am looking for a column that counts every unique location that starts with 1A, then a seperate column for 1B, 2A, 2B, 3A, 3B and so on. Thank you in advance for any help.

    Re: Copy,Paste,Email macro not pasting formatting correctly.


    Thank you very much, worked like a charm. I ended up with


    Thank you again.

    Hello everyone,
    I have a sheet I use at work that I made a email button on that will copy a certain sheet and paste it to a new workbook then email that book out. I am running into a problem where I am pasting to the new sheet but the merged cells from the original sheet are not merged after the information is pasted to the new workbook. Any ideas? I have the macro set up to only paste the formatting and the values as the original sheet has formulas that I do not want to past to the sheet I email out.

    Re: Auto email 1 sheet from a book and copy from sheet past to email body.


    And it worked perfectly, just changed a few things to fit my sheet. Shoulda looked more in depth there when I started this, woulda been done a lot quicker... Thank you for pushing me back there Roy and thank you for your time.


    Here is what i got from Ron De Bruin to accomplish the emailing of a selection in the body of a email;

    Re: Auto email 1 sheet from a book and copy from sheet past to email body.


    Just incase anyone wanted to see the code without downloading the sheet, this is where I am :

    Re: Auto email 1 sheet from a book and copy from sheet past to email body.


    forum.ozgrid.com/index.php?attachment/65568/ Ok so I went back to Ron de Bruin's email code example's and got a snip-it of code that took away the formulas. So I now have 1 part of what I wanted to do complete. The email button will now email just one sheet from the work book with just the values and formatting leaving the formulas out. Now I just need to figure out how to copy a range of cells from a worksheet and paste them into the body of a email.

    Hello everyone, I have managed to get this far with a lot of research and trial and error. I have a macro built in there to email one of my reports but I really need it to just copy the values and the formatting but not the functions to the file it emails. Second I need a separate macro that will be able to select A2:B5 from report C and paste that information to the body of a email. Its also worth noting that while I have 2013 here at home I will have to do this in 97 at work. Thank you in advance for any help.

    Re: SUMIF with >,<, and wildcard


    Works perfectly, Thank you. If its not too much trouble could you please break it down and tell me exactly what the formula is doing? I would like to learn why it works, so I can understand how to use it in the future. I get SUMPRODUCT is adding up array B2:B31, and the RIGHT function is pulling the 4 digits from C2:C31. I don't understand what the asterisk is there for or the +0. and the <> criteria look like they are part of the RIGHT function?

    Re: Pull data from every worksheet and put into a new workbook


    Hello, I'm fairly new to VB and I am sure you can get a cleaner solution with VB but I was wondering if you could just use

    Code
    =IF([Recipes.xlsx]Sheet1!B7=0,"",[Recipes.xlsx]Sheet1!B7)

    then just change for the appropriate sheet. you don't even need the recipes workbook open for the main to update. Once the information is saved in the recipes book the main book will display it whether the recipes book is open or not.

    Re: SUMIF with &gt;,&lt;, and wildcard


    Hello and thank you for your reply, I am trying to get it to ignore the letter of the starting slot and sum the eaches from every starting slot less that 4000 in one box and sum every starting slot above 4000 in another. When the formulas are correct they should return 9016 for the total eaches from slots below 4000 and 353 total eaches from slots above 4000. I was trying something like

    Code
    =SUMIF(C:C,"<?4000",B:B)

    for the less than 4000 but this returns 0, in my sheet I used the asterisks and got the same result. My thinking was the ?4000 or *4000 would tell it to ignore the first letter and just look at the 4 digit number to determine the less than.

    Hello everyone, I have searched and searched and have not found the correct answer. I have attached a file named TripSummary. In this sheet I am trying to add the eaches in column B based on the starting slot in column C. The starting slots are always a letter followed by a 4 digit number. I am using

    Code
    =SUMIF(C:C,"H*",B:B)

    to sum them based on the letter but I also want to sum all starting slots below 4000 in one cell and above 4000 in another cell regardless of the letter they start with. Now I know I can do it with

    Code
    =SUMIF(C:C,"H1*",B:B)+SUMIF(C:C,"H2*",B:B)+SUMIF(C:C,"H3*",B:B)

    and so on for all the letters but I am thinking there has to be a better solution. Thank you.

    Re: Conditional formatting of a row based on value of a cell


    Yea I was already looking at doing that way I am just worried about hitting problems down the line as the conditional formatting formulas don't copy down very well with the format painter. For instance the formulas for A2:G2 read:
    Rule:

    Code
    =$A2>0


    Applies to :

    Code
    =$A$2:$G$2


    and that is perfect. but if you paint the formatting down every row after 2 reads:
    Rule:

    Code
    =$A3>0


    Applies to:

    Code
    =$A$3:$G$50


    Now having said that the formatting appears to be working correctly even though the rule is pointing to A3 the cells actually look at cell A in whatever row they are in. I would just feel safer if the formulas looked correct.