Create report in the another sheet filtering two column values

Dear Friends,

I need to create report (just summing up) based on values in two different columns. I need a VBA to do it as the column values may vary at any time.

I have attached a sample workbook for your kind reference.

I hope someone will come forward to give me a solution.

Thanks in advance,

Have you considered using a pivot table and refreshing as appropriate?

Dear Sir,

It's a good idea. But my sheet is protected and the usage of PT is not that much familiar to my friend who is going to use it regularly. Hence, a macro type of solution will be best suitable for him.

I hope, you will kindly help me to solve this problem.

Is there a particular reason why you double posted this?

Try this

Please Login or Register  to view this content.

You might try to fire this using a worksheet change event by adding the following to the appropriate sheet tab in the VBA editor.

Please Login or Register  to view this content.

Dear Mr. MRICE, the Forum Guru,

What more I can expect? This is the wonderful solution which makes my work so easier to update. Yes, again thanks for your kind solution.

The way you understand the problems and providing the solution is amazing. Just, I am speechless. Thanks, Thanks a lot.

Dear Sir,

Thanks for your code. It works very nicely.

I need a small change. It must be a simple for you which is very difficult when I tried.

At present, both data and report are coming in the same sheet.

Whereas, I need that needs to be separated, i.e. Data should be in one sheet and the code should copy the report in another sheet (because, the data sheet is protected).

Can you please help me to solve it?

Thanks in advance.

@ everybody.

Please be aware that this is a double post, although it may not be immediately apparent.

http://www.excelforum.com/excel-prog...e-columns.html

Dear Teylyn & other Friends,

First of all, I would like to explain the differences between these two posts, as it creates a lot of confusion to few as they think that these two are double posts:

1. http://www.excelforum.com/excel-prog...e-columns.html
2. http://www.excelforum.com/excel-prog...mn-values.html

The only common thing is that both are based on expenditure. The differences are:

1. The First link is based on:
Report based on Three columns, Different dates & periods, expenses reports are analysed in depth (For each Item & Col F), Expenses / Items may increase in Row-wise.

2. The second link is based on:
Report based on two columns, Expenses are in Column-wise, Expenses/Items may increase in both ways.

I hope this explanation will help them to understand my these two as different posts.

Dear Teylyn, I do agree with you that I should not assume that those whoever posting are not male. Though it is a common problem, I will rectify in future.

Thanks,
acsishere.

Dear Coder,

Thanks for your response on TRS2.XLS. Actually it is the reply for my another thread http://www.excelforum.com/excel-prog...e-columns.html.

It is also working but very slowly. Besides, in case of increase/change in item names & expenditures it will be difficult for the end-user to adjust the same as per the requirement.

Hence, I feel, a VBA will be the better solution to solve it. Isn't?

Thanks in advance,

Dear Friends,

The above MRICE's code is perfectly working. The only change I need is that the report is at present generated in the same sheet which I need to shift to another sheet (So that the Data & Report are in separate sheets).

Thanks in advance,

Well it was just an example of one way you could approach the problem rather than a fully thought out solution, for example speed is an issue because each instance of the UDF is looking at every cell in 3 columns i.e. 196608 cells - that's bound to be slow, but if you reduce the number of cells that the formula is looking at by using dynamic named ranges it would be relatively snappy.

e.g.

TRS2.xls

You could of course do something very similar with a sub routine if you preferred.

Dear Coder,

Yes, it is working very nicely. Thanks a lot for suggesting me to dynamic named ranges.

Whereas, the file you attached is the solution for my another thread
http://www.excelforum.com/excel-prog...e-columns.html. Thanks for the same. Now the speed is highly increased.

Thanks for the solution.

Can any one give me a solution to this thread - i.e. modifying the code by MRICE to make the report in another sheet?

Thanks in advance,

Dear Friends,

Any help? Please...

Thanks in advance.

see, this confusion is the result of double posting and pursuing the same problem in two different threads. You now have a cross-over of posts from two threads and it's quite difficult for the contributors to figure out which solution relates to which post.

Dear Teylyn,

Yes I do agree. Though it is not a double post, from distant view it seems to be. Whereas, these two have a lot of differences between them (I have explained the same in this thread).

However, is it possible to get the solution for this thread?

Thanks in advance.

Amendment to my original code to place the data on a separate sheet.

Please Login or Register  to view this content.

I've called the second sheet ("My unprotected sheet") for this example.