• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidated report

M

moizs

New Member
Hi All


I have to sheets one contains the sales details i.e date,invoice no, customer name, amount, salesman.


Sheet 2 contain the sales return information i.e date, SR.No, Customer Name, Amount.


I want to create a consolidated report which gives the total of sales and sales return and nett sales for all the customers.


The report gets updated when new data is added in sheet 1 and sheet 2.


Please help
 
Hi moizs,


May I request you to upload your sample data? To do the same plz follow the link below:


http://chandoo.org/forums/topic/posting-a-sample-workbook


Meanwhile, regarding your question, you can use the following method to make your data range dynamic:


Assuming you have 5 column of data(row 1is your header), go to name manager(ctrl+F3 keyboard shortcut),hit new to give any name and in refer to section write the following formula:

=OFFSET('Sales Details'!$A$2,0,0,COUNTA('Sales Details'!$A:$A,0)-2,5)


This will make your 5 column of datarange dynamic. Whenever, you add or delete any data(any rows), the range will automatically expand or shrink, respectively.


pass this dynamic name range to your (lookup)formula to update the info.


Looking forward to your sample dataset in order to provide you more exact solution of your problem.


Regards,

Kaushik
 
Hi Kaushik


Thanak you for the reply.


Please find the sample workbook, I have used sumproduct in the report the problem is that when i add data i would have to change the range each time however I do have the option of taking a larger range but if alternative is available I would like to go for that.


https://dl.dropbox.com/u/96239378/Sample%20Book.xlsx
 
Hi moizs,


Thank you for uploading the file.


I have applied same offset-counta formula(as I mentioned above) to make the data range dynamic. The named ranges are:


1)Sales_DynamicAmount:This will make the amount data range dynamic at 'sales' sheet

2)SalesDynamicCustomerList: This will make the 'customer names' data range dynamic at 'sales' sheet

3)SR_DynamicAmount:This will make the amount data range dynamic at 'Sales Return' sheet

4)SR_DynamicCustomerList:This will make the 'customer names' data range dynamic at 'Sales Return' sheet


Then I have passed these named ranges to your sumproduct formula


Whenever, you will add or delete any data at Sales or Sales Return sheet, your formula will be updated accordingly.


Check the file here:

https://hotfile.com/dl/165762827/563fd60/Sample_Book_NameRange_to_make_data_range_dynamic.xlsx.html


Let us know if this helps.


Regards,

Kaushik
 
Hi Kaushik


Thank you for the file.


The file doesnt serve the purpose because the report is updated for those 4 customers only but if we add a new customer that is not reflected in the report.
 
Ok..I did not understand this in your last post...my apologies!!


If that is the case, then I think VBA could be an easier way to achieve this.


Would that be fine to you If I apply VBA to get this work done?


Regards,

Kaushik
 
Well Kaushik any other option you have, I don't know VBA.


Is it possible tht we make each column as tables and then apply sumproduct.


Regards


Moiz
 
moizs,


Why don,t you apply data validation in a cell with the unique customers which fall under sale and sales return sheet; and then link that cell in your sumproduct formula. So you will have only one row of data with all the information for the customer you will select from data validation cell. By this way, you can avoid the process of adding rows as the data would be added in parent sheet for a different customer.


But in this case only data validation list needs to be updated with the unique list of customer if a new customer is added in the data. That we can achieve with formula and named range. does it sound like a feasible approach to you?


Kaushik
 
Hi moizs,


First of all, my apologies for such delayed reply. I actually got stuck with lot of works due to which I could not work on this yesterday.


Can you plz check the file here:

https://hotfile.com/dl/165876692/7c5f1af/Consolidated_report_moizs_Chandoo_forum.xlsx.html


Layout of the work:


At Sales sheet:

1)Made col C dynamic with a named range = dynlist1

2)Made col D dynamic with a named range = dynamountsales


At Sales Return sheet:

1)Made col C dynamic with a named range = dynlist2

2)Made col D dynamic with a named range = dynamountSR


At Report sheet:


1)Wrote the following array formula (press ctrl+shift+enter) at A2 to get the unique list of customers from col 'C' of 'Sales' and 'Sales Return' sheets:


{=IFERROR(IFERROR(INDEX(dynlist1,MATCH(0,COUNTIF($A$1:A1,dynlist1),0)),INDEX(dynlist2,MATCH(0,COUNTIF($A$1:A1,dynlist2),0))),"")}....copy it all the way down as you need(I have copied it till row number 300....but you can do it more)


2) I have also modified your sumproduct and net sales formula to handle the error part


Now, I would suggest you to add rows with new customer's data(or existing customer with new sales data point)at 'Sales' and/or 'Sales Return' sheet and see how the (report) formula is updated at 'Report' sheet.


Please let me know if you face any challenge.


Regards,

Kaushik
 
Hi Kaushik G


can you give me your mail Id if any doubts then i can contact with you


With Regards


Patnaik
 
sgmpatnaik,


Surely...here it is:


pharmacyjukaushik@yahoo.com


But May I ask you one quick question?


This post was started by moizs. Are you working on similar kind of a problem what moizs has?


Regards,

Kaushik
 
Thanks Kaushik G


No Just i am refereeing the posts in Chandoo Group


What type of Questions are in there and what is the solutions


That's all


and Thanks Once Again for your Quick Replay


i send a test mail for your kind knowledge


Thanking You


With Regards


Patnaik
 
Hey Kaushik Thanks a ton, but I dont understand the functions but never mind will check the functions myself and will drag the column down to a huge no.
 
moizs,


Glad to help you..


But did you check the formula by adding some new data at 'sales' and 'sales Return' sheet?


Anyways, here is the explanation for the formula at A2 of 'Report' sheet:


Note: I assume, you have already understood the dynamic named range formulas (with offset-counta) which I already explained in one of my aforementioned posts.However, please let me know if you need any further explanation for the same.


The entire formula is:

=IFERROR(IFERROR(INDEX(dynlist1,MATCH(0,COUNTIF($A$1:A1,dynlist1),0)),INDEX(dynlist2,MATCH(0,COUNTIF($A$1:A1,dynlist2),0))),"")}


Let's break it up and understand it step by step:

the first part:

INDEX(dynlist1,MATCH(0,COUNTIF($A$1:A1,dynlist1),0))


The COUNTIF($A$1:A1,dynlist1)partreturns an array containing either 1 or 0 based on if $A$1:A1 is found somewhere in the array dynlist1


becomes:

COUNTIF("Customer Name",{A; A; B; B; C; C; D; D; F; F} )


and returns:


{0;0;0;0;0;0;0;0;0;0}


This means the cell value in $A$1:A1 can´t be found in any of the cells in the named dynlist1. If it had been found, somewhere in the array the number 1 would exist.


Note:As you know, the match formula returns the relative position of an item in an array that matches a specified value.


Hence, MATCH(0,COUNTIF($A$1:A1,dynlist1),0) becomes:


(0,{0;0;0;0;0;0;0;0;0;0},0)


and returns 1 (We are getting this value for the first 0 in {})


Now =Index(dynlist1,1) becomes:


=Index({A; A; B; B; C; C; D; D; F; F},1)


And it returns A


The same logic is applied in second part where we have used dynlist2.but this part comes under 'value_if_error' argument of iferror formula.


Now see how we have used IFERROR in this formula.


=iferror(value, value_if_error)


=iferror(INDEX(dynlist1,MATCH(0,COUNTIF($A$1:A1,dynlist1),0)),INDEX(dynlist2,MATCH(0,COUNTIF($A$1:A1,dynlist2),0)))


Here,


value = INDEX(dynlist1,MATCH(0,COUNTIF($A$1:A1,dynlist1),0))

value_if_error = INDEX(dynlist2,MATCH(0,COUNTIF($A$1:A1,dynlist2),0))


Finally we wrap the entire formula with another iferror (for 2007 version; you have to use if(iserror...in case you use 2003 version). We have used IFERROR to return blank[()= empty string] in case any error happens.More precisely, since we are dragging it say for more 300 rows at report sheet but we do not have 300 unique customers either in sales or sales return sheet, we would obviously get error after the formula does not find any match. To tackle this, we have used iferror to return blank if error happens


Note: In countif part of the formula, we have used relative and absolute reference Because of which, When you copy the array formula down the countif formula range ($A$1:A1) expands.


The first cell, A2: COUNTIF($A$1:A1,dynlist1)


Second cell, A3: COUNTIF($A$1:A2,dynlist1)


and so on.


Hope I am able to explain the logic, to some extent, to you...


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top