Posts by pukks

    Hi

    I'm trying to paste a value from oneworksheet to another (invSheet toPayment sheet), which sounds simple enough.
    Ihave 4 ranges in the Payments sheet. These are PayNames, SeptPaid,OctPaid andNovPaid.
    Myissue is that I need to find the name in the PayNames range that matches cell B4 on the inv sheet(is also on B16 onthe payment sheet), then paste in the amount from the produced invoice(Invsheet) - in this case £40.(B7) into the appropriate column for the month being used.

    Eachmonth a different range for pasting purposes is used. I've attached a sampleworkbook .
    Ihope this is clear.
    Dave

    Re: Return list of values based on adjacent cells


    Thanks, that's working fine. Wanted to sort the list but it appears blank cells cause an issue when sorting from A to Z and they are placed at the top of the list
    Guess I could replace them with ZZZ and do a find and replace.


    thanks for the help

    Hi Forum

    I’m trying to create a list of names for a register. I have two sheets on the attached example where the attendance is in the source sheet and list of names ( those in attendance) in the register sheet. Sheet is blank apart from the days of the week.
    I was going to use MS query connecting one worksheet to another to only return the name of the child who had a value of 1 for a given day, but this means 10 different queries for the 5 days of the week.

    Is there a more efficient way of achieving this. Should I be using the offset command ?
    Thanks
    David

    Files

    • REGTEST.xlsx

      (10.63 kB, downloaded 70 times, last: )

    Re: Conditional Average By Criteria


    sorry for not getting back to you guys earlier. I'll also read the "anatomy of a good thread title" as I often struggle with my thread titles.


    I'm assuming if I want to include a 3rd criteria I simply extend the formula accordingly, ie the cycle type in column c, I add the range $C2:$C11 and check this against "cycle type" which would appear in a cell in L4 (replacing "vacuum sintering")


    thanks


    Dave

    Hi forum


    Firstly may I apologise for the thread title if it seems a bit vague as I'm not sure on which approach/solution to use.


    I have a table of data attached(stripped down version) in the workbook below which I need to query information from. The resulting data must be shown in another summary table called " table1 vacuum sintering at 1360"


    Each area(column A and column J) must match and also the cell K7 must be the average of all the corresponding cells(HC COR Mean) that have the criteria of being at the temperature 1360 (cell N4). Therefor the average would be 4.0
    Obviously I need to repeat this scenario for other temperatures ie 1410 in the other table and other measurements ie COM Mean


    I'd normally use a pivot table to summarise but I've been told to use this format.
    I did try vlookup but didn't think this would work in this case.
    Wasn't sure if index match would be the way to go with this one either.


    Any help on this greatly appreciated.
    many thanks


    pukks

    Re: Delete Rows If Data Exists On Other Worksheets


    I'll just need to delete all the rows that contain any data.


    thanks


    pukks[hr]*[/hr] Auto Merged Post;[dl]*[/dl]tried the following but my result was that the contents in the data sheet were cleared and the other sheets had all their data still in it. I'm invoking the macro from a button and the code is place in the "this workbook" part of the vba project. Any ideas as to why the code isn't working.


    many thanks
    Dave


    Hi.


    I have a workbook thats starts life with just one sheet. This is always called "data".
    On the click of a button several other sheets are generated with the new sheet names being of all of the 'distinct' items in column A of the data sheet. So if my list in the data sheet contains items x,y and z so the sheets are named accordingly. And I thank this forum for the code to do this.
    Another macro beckons for the next bit.

    I now however want to delete all of the rows with information in that exist in all of the other sheets other than the 'data' sheet. How can this be achieved if the sheet names change regularly.


    Hope this is clear
    thanks
    pukks

    Re: Paste Rows Into New Sheet When Criteria Met


    I would have preferred the pivot option myself but the end user will have the workbook linked to powerpoint as a screen saver. Therefore each sheet needs to be separate.


    HTH


    Pukks



    Problem now solved code working,thank you Mr Hawley and Co

    Re: Paste Rows Into New Sheet When Criteria Met


    Hi


    I used the code option


    but came up with a compile error, "invalid or unqualified reference"
    The line highted in the vba was

    Code
    1. wSheetStart.AutofilterMode = False

    and the word AutofilterMode was highlighted, Any ideas


    thanks
    pukks

    Hello Forum


    I have an example spreadsheet attached which contains 5 sheets. The 1st sheet ("raw data")contains the master data and the other sheets (which are blank at the minute) are the destination sheets.


    I'm after a macro that will iterate throught the "raw data" sheet and when it finds the first cell in column B that starts with 'POPRS' it copies and pastes this and the remainder of the row (through to 'H') into the new sheet 'POPRS' and continue to do this for all instances where column B cells start with 'POPRS'. Likewise with the rest of the Grades in this 'Raw Data' sheet.ie Instances of PORAW,PORTP,POPRS etc. Column 'A' must be pasted also
    Hope I've made this clear.


    many thanks
    Pukks

    Hello Forum


    Here's my dilema.
    I have as an example two products. One called PORAW VC and the other PORAW WC30
    I need to extract from the 7th character (in this case) to the end of the string. The string length always changes and to complicate matters some products dont have a space in their name.


    eg PORTP060DUS ( here I want to extract just the 060DUS )
    PORAW VC (Just need to extract VC)
    PORAW WC30 (Just need to extract WC30)


    I suppose this is some sort of dynamic extraction


    Hope this is clear


    pukks

    Re: Odbc External Database And Query


    I may be wrong but I think the odbc data source thats set up needs to be of a different type.
    ie MS Access i think needs to use a "file.dsn" and MS Excel a "system.dsn"
    Have a look in Control Panel/ My data sources to see which is active and set up for sybase as you may have to create a new data source with the sybase driver.


    HTH
    Pukks

    Hi Forum


    I've been playing around with a bit of sql as I need to create an sql pivot query that connects to an SQL2000 server.The "pivot" function is only available in sql05.


    Unfortunatley I'm not having much luck with displaying or retreiving the results in the order in which they should be.


    My sql qry attempt is thus:
    SELECT


    MAX (MLIADE.KFITNO), MAX(MLIADE.KFBANO),
    MAX(MLIADE.KFANCL), MAX(MLIADE.KFACVA),


    'W' = MAX(CASE WHEN KFANCL='W' THEN 1 ELSE 0 END),
    'COM'=MAX(CASE WHEN KFANCL='COM' THEN 1 ELSE 0 END)
    from PROD3_Staging.dbo.MLIADE MLIADE
    where (MLIADE.KFFACI='155') AND (MLIADE.KFITNO='poraw wc25d') AND (MLIADE.KFBANO='0524')
    group by


    MLIADE.KFACVA


    I may have got it totally wrong above as the output I want to display are attached in jpeg image below.
    Note I will not be outputting the data to excel.
    The field KFANCL is the Element(s) Measured ie W,MO,CT and KFACVA is the respective value.


    Hope this makes sense


    thanks
    pukks[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I forgot the attachment

    Hello Forum
    Found another thread entitled similar to this one but no luck for an answer.


    I have attached a sample workbook where in using the sumproduct function I get a #VALUE
    error returning.


    Basically I'm trying to sum all the weights of all the cells that begin with POREJ.
    Cells C11:C22 contain numbers(weights) but not all the time and cells E11:E22 contain Item descriptions, but not all the time as they are sometimes empty too


    My formula (h13)seems to work when there are no empty cells when I adjust the range accordingly say to C13:C15 and E13:15


    Any ideas.


    Pukks