Posts by alibaba

Re: Reverse Search

got it, thanks

forum.ozgrid.com/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.

Re: Combining a number of worksheets into a single worksheet

The complier stops at lngPasteRow = ..... and say
no object variable defined or With variable block

Re: Combining a number of worksheets into a single worksheet

The complier tell me that there is a missing IF. Where should I put it in?

Re: Combining a number of worksheets into a single worksheet

In addition, there are some modificaton in the worksheets, there are other data in the worksheets and the tables that I want to copy start from A11:P19, A11:P13,.......

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

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.

Re: How to use SQL to select students whose pass fail in one of the subjects

To make it clear i want to display name of students who fail in only one of the subjects. The SQL is select name from table where..............................

Dear helper,

How to use SQL to select students whose pass fail in one of the subjects. Here is the table

Name Chinese Eng Math
S1 2 50 57
S2 57 33 91
S5 23 31 78

using AND, OR seems quite long.

Can it be written shorter?

Re: find and replace for special string 5*, 5**

Quote from jindon;619589

what do you mean by ??? ?

I need to find using 5~*~* first.

I got it. Thanks.

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

Re: find and replace for special string 5*, 5**

What do u mean by 5~* ??? Does it mean that I enter 5~* in the find textbox?
But 5* cannot be found.

hI,

In an column contains grades (1, 2, 3, 4, 5, 5*, 5**, U)
I want to replace 5* with 6, 5** with 7 and when I enter 5* in the find and replace box, the number 5 is found also which is not what I want.
How can I solve it?

Hi,

There is error in using sumproduct if the last array contains text. Can it be solved?

see attachment.

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.

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.

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.