Posts by alibaba

    ozgrid.com/forum/core/index.php?attachment/70563/Sometimes using vlookup to locate a specific piece of data is easy. This time I want to do a 'reverse' search.
    As in the test.xlsx, each kid may only select one type of fruit. What I want to do is: input the name of kid in F3 and his/her favourite type of fruit will be shown in G3. What is the formula in G3?
    Thanks for your help.


    Sorry, When input Peter, should be Banana.

    Files

    • Test.xlsx

      (9.44 kB, downloaded 94 times, last: )

    Re: Combining a number of worksheets into a single worksheet


    Hi Robert,


    Thanks for your great help. In the VBA, all the tables in the worksheets (except Master) will be copied. If there are other worksheets in the workbook and the worksheets that I want to cope with are A01, A02,....A30. Does the VBA still work? If not, how to modify it?

    Dear helpers,


    I am new to VBA and have the problem:
    To combine worksheets into a single worksheet we always use copy and paste but if there are many worksheets, say 30, it will spend a lot of time in selecting and copying.
    I want to create a button in sheet1 and onclick it, those tables in the worksheets(named A01,..,A30) will be copied to sheet1, say from sheet1!A1.
    The worksheets will be of the same number of columns:
    A1(name), B1(date), C1(number), D1(price)


    but different rows of data.
    Can the VBA code count the number of rows in each worksheet?


    Thanks for your help.

    Re: displaying values from a number of worksheets


    Displaying values from different worksheets



    Further to previous post.
    I have 6 values in sheet2, 2 values in sheet3, 10 values in sheet4. To display those values in sheet1, I can copy those values manually but I want to automate this work. What formula should be put in sheet1?
    The problem is:
    the numbers of values maybe different in different worksheets, how to put them in a single column in sheet1?
    I have attached the xls for your ref. I want a formula in sheet1!A5 and copy down.
    Thanks for your help

    Files

    • sample.xlsx

      (11.07 kB, downloaded 83 times, last: )

    I have a text in sheet2!A1, sheet3!A1,...sheet20!A1 and I want to display those values in sheet1!B1:B20. To automate the work, I put the text 'sheet1' in A1, 'sheet2' in A2....'sheet20' in A20.
    What formula should be put in B1 and copy down to display the values from those worksheets?
    Can I use INDIRECT()?
    Thanks for your help

    In E2, I want a formula that displays the amount based on the table and what StudA has chosen.
    vlookup can help.
    But how to decide which day studA has chosen?
    That is,
    StudA chose Mon and was given $200, StudB chose Wed and was given $300.
    A formula is required to display such values in E2:E4
    Thanks for help.

    Files

    • Test.xlsx

      (9.85 kB, downloaded 92 times, last: )

    Hi,


    Sometimes we convert database file to Excel file but the structure is not what we want. See db.xls, B2:D20. I want to rearrange the cell range B2:D20 to the format F2:R6. Put the corresponding numbers or letter (D3:D20) in the range G3:R6. If there is no sale level in certain month, put 0.


    Is there a formula that can be put in the cell range G3:R6 to achieve this?


    Thanks

    Files

    • db.xls

      (25.09 kB, downloaded 78 times, last: )

    Hi,


    Can anyone help me to count the following?


    I want to count the number of students whose Chinese grade >=3 AND English grade >=3 AND Math grade >=2.


    In sample1.xls, only 3 students: John, Jimmy and Oscar can get Chinese(>=3) and English(>=3) and Math(>=3) so the number is 3(as shown in I3). It seems that it is very easy to count manually. However, I cannot write a formula that can be put in I3 to get the result.

    Files

    • sample1.xls

      (28.67 kB, downloaded 76 times, last: )

    Re: counting differently


    Sorry that I didnt make it clear.
    In sample1.xls, only 3 students: John, Jimmy and Oscar can get Chinese(>=3) and English(>=3) and Math(>=3) so the number is 3(as shown in I3). It seems that it is very easy to count manually. However, I cannot write a formula that can be put in I3 to get the result.

    Files

    • sample1.xls

      (28.67 kB, downloaded 78 times, last: )

    Re: counting differently


    Sorry that I didnt make it clear.
    In sample1.xls, only 3 students: John, Jimmy and Oscar can get Chinese(>=3) and English(>=3) and Math(>=3) so the number is 3(as shown in I3). It seems that it is very easy to count manually. However, I cannot write a formula that can be put in I3 to get the result.

    Files

    • sample1.xls

      (28.67 kB, downloaded 75 times, last: )