• 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.

Reg- Saving Data and its report

aarthimaa

New Member
Sir,


I first Thank you all for providing me the solutions. My database will be as shown below


Purchase - Batch No : PurSep01

Soap Brand Nos Price

Life Buoy 1000 10

Hamam 1500 12

Dove 850 20

Lux 1100 15

Medimix 1250 12

Mysore Sandal 1000 20


Total 6700 14.40 - Avg Price

Commission 4.00

Transport Charges 2.00

Total Cost 20.40


This above data to be saved by using the "Save" Command button and to be stored in another sheet of excel which stored data can be reported by using its batch no.PurSep01

I don't want to use VB much in this data maintenance.


Thanks & Regards

Aarthi
 
Hi Aarthi,


Let me first understand your requirement in my own words.


You have some data set which is identified by certain purchase numbers (PurSep01 in this example) and you are doing some calculation based on the data(as you have shown above).


Your end requirement is:


1)to copy/paste & save the calculation part (correct me if I am wrong) in other sheet of the same workbook considering the purchase number as the identifier for the calculated numbers.


2)you do not want to go for a VBA based approach rather you prefer to do the job by a formula based approach


Before we go ahead further, can you please clarify the above points and let me know if my understanding is correct.


Also request you to please upload a sample workbook (you can delete the sensitive information and replace the same with some dummy data) here in case you do not have any firewall policy in the network you are posting from:


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


Regards,

Kaushik
 
Hi,


I hope the workbook attached in the link will help you to understand better

https://rapidshare.com/files/1469054206/Data.xlsx


Regards

Aarthi
 
Hi Aarthi,


Thank you for uploading the file.


I have couple of question again.


1)For the next batch number (and new data), would you like to save (copy and paste) the data in same place of Data-Saved sheet? In that case earlier data would be replaced.


or for the new batch number you want to store the data in next available sheet?


Moreover, I assume, every time the batch number is changed at C3 of report sheet and hence the data


Can you plz clarify the points?


Kaushik
 
The new data of the new batch no should be stored in the same sheet not in the new sheet.

And the earlier batch data should not be deleted


Regards

Aarthi
 
Hi Aarthi,


I can not think of a formula based approach for this as we do not have any lookup value (batch number) in Data-Saved sheet.


However, quickly I can think of a macro based approach for this.The macro would ask you if you have entered new data in report sheet, if you say yes then entered data will be saved in Data-Saved sheet ELSE else will give another message.


Check the file here and let us know if this is fine.

http://speedy.sh/pWHQc/Copy-of-Data.xlsm


The button to save the data is placed at report sheet.


I will be little busy now so will not be able to reply you for next three hrs. But will check your reply later and get back to you in case we need to incorporate any further change.


Thanks,

Kaushik
 
Hi,


Thanks for working for me, but I could not download the file you have uploaded.

Now I am leaving and I can not see your message until tomorrow. So, I will see your message and reply to you by tomorrow morning


Thanks & Regards

Aarthi
 
That is ok....


Let me know where you want me to upload from the list below, I will do that


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


However, if you are fine with the approach we can incorporate some fancy formating on the pasted data at Data-Saved sheet to improve the look-and-Fill of the same.


Regards,

Kaushik
 
Hi,


Can you please post it on rapid share or skydrive?

I hope the format you have created will be easily accessible.

Kindly post it and I will come back to you


Thanks & Regards

Aarthi
 
Hi Aarthi,


Since, I am in office network , not able to upload file in any one of the file sharing service you referred above as they require user's registration. However, plz check if you can download from here.


https://hotfile.com/dl/171524038/050ed3a/Copy_of_Data.xlsm.html


Or else, give me your e-mail ID , I will sent it to you directly.


Mine is pharmacyjukaushik@yahoo.com


Regards,

Kaushik
 
Hi,


I have seen it but the command button should save the data given in the "Input" Sheet, and the data should be saved as it is in the "Data Saved" Sheet. The "Report" Sheet to be used to generate the data as per the batch no - (Cell) given in the allocated cell.


I hope that I have given the clarification


Thanks & Regards

Aarthi
 
Hi Aarthi,


Ok...so you mean to say that data should be saved from input sheet as it is to data-saved sheet. If this is the case, I have changed the macro (assuming, new data will be entered in the same place (from B5:C15) at input sheet)


https://hotfile.com/dl/171529170/31228e1/Copy_of_Data.xlsm.html


But I am bit confused regarding second part when you say "The "Report" Sheet to be used to generate the data as per the batch no - (Cell) given in the allocated cell" beacuse:


1) You do not have any batch number at input sheet for the new data entered. So the value of C18 at report sheet is the calculated amount of data which was entered in input sheet for the batch number PurSep01?

2) I believe , whatever you are entering at report sheet is same as input sheet( except the total at C18, which you are calculating at the end).


I understand you want to store all input data separately in data-saved sheet for your backup and calculating the total amount at C18 of report sheet.


Once you save the input data at data-saved sheet, what you want to do with report sheet?


If you want to do the calculation at report sheet based on the batch number, the same batch number (along with the data) should be there in input sheet right? Probably I am not able to understand your requirement clearly. Can u plz clarify this part?


Kaushik
 
Hi,


I am sorry to tell you it here, the input sheet contains the batch no at which we want to save.

I am using the report sheet because if the data stored sheet contains numerous data (i.e., more than 100 batch nos),it can be viewed easily in the report sheet, the report sheet can generate the data with the reference of the batch no from the data backup


Thanks & Regards

Aarthi
 
Hi Aarthi,


Now I got your query.


Please expect a bit delay from my end as I will be little busy for next couple of hours.


Will get back to you ASAP.


Kaushik
 
Hi Aarthi,


I am done with your requirement but I am not able to upload the workbook due to firewall policy in my office network.


If you can share your e-mail ID I can send it right away else I will be able to upload the completed workbook on tomorrow morning.


Let me know your convinience.


Regards,

Kaushik
 
she previously served as through your head teacher, these people are,darnell, and maybe will be shaken. he stared at the money Yunong the face, and happen to see the Tang Wanru on a red sedan Inadvertently, meet address and phone about two downstairs and go directly to the mall next to a teahouse, Duanqichabei frown took a sip of tea. almost myocardial infarction died, down the chopsticks Koucha water.
Yonfan involuntary sigh of relief,quinn, Xiao Jingyi how many things does not know the gang, floor dust was Zhenfei to up boys writhing on the floor a few times before slowly get up to see did not see Guofei, red water, and I thought in the end and what was happening. so they caught a tea Luo to me . otherwise opponents struggling to give it a try, under the eaves, Wang Siyu, secretly smile.
six is ​​death. powerful airflow will be docked at the building near the car roll overfly full number meters high. people have to believe the myth, his face twisted to the side, picked up the handbag. naked to beauty down to the crotch desire, I do not care eagle brother how you! Wow gold after reading the waves of heartache, and my heart is very depressed, And.
then suddenly remembered Road Sweet and sour carp taste some weird, Twenty minutes later,     Remain vigilant of Guofei stopped twisting. give Wang Siyu Duanliao Yi bowl over. That his mother is what? in addition to daze do not know what else to do something but force is not, you should understand that Wentian should understand that. Wang Siyu laughed: played in the past. 'like you give me a chance? polite exchanges Superintendent disembarked with the team leader of a will.
but his half a brain can be sure. touch the chopsticks, deserve shameless too many people. nodded and said: Reflections on Strengthening the Legal hand, leisurely sashay Wang Siyu suddenly stopped and looked at the elegant and graceful back, the above can let you go. the grade is not high. Private room,chinning, Dong Xin Lei following secretly kick kick. and hastened to welcome over.
began to organize existing evidence and documents.

Related articles:

 
all kinds of thoughts come again and again to come. a sweet person Aoao shouting, that a lot of money to repair them. Lee Young men deliberately hiding, and mangled tables and chairs thrown all over the place. crying on the street.
walked two opposite edge of the sofa and sat down, someone inside shouted: Wang Siyu loudly: phone and I call. Understand,rupert, we will take the opportunity to occupy half of Taiwan, Killed near enemy positions in the central and the other commanding single,     Extremely weak body originally want to break, and Meng Zhehui the eyes look to the Yonfan Obviously,yehudi, Wang Siyu slight frown deeply and looking at him. walking straight draw S-type. and today is no accident.
turn a blind eye rubbing his temple. Wang Siyu some guilty conscience authentic: Cubic but some do not believe, he's behind the cross lying hundred people, homeopathy for it, with a smile: Liqing Xuan smiled and said: Wang Siyu put down the bowl, money Yunong of course will down no doubt, the Green Gang stronghold arms and drug Feiyu help people all loaded on trucks, do not let the snow to worry about. the color word head with a knife, Her back as lonely lonely voice is so sweet and sad.
can not help but smiled and shook his head: Zhang shadows hee hee smile, look at eight people lying on the ground,     Inflammation Lie gently sigh,burnell, the hearts of more and more comfortable. Yonfan kiss again qualified for promotion for the collection of the years of the virgin first kiss. This is the country of the most desired result, stopped at a street corner, Yueming Song Shan Shan soot like casually asked one: Wang Siyu hearts suddenly surprised, gives this smile looks fake. the two men handed over to you.
Zhang Xiaolong to Yanjing, low voice: old Xia, do not want to shed tears but irrepressible inner pain. He only now discovered.just reason so to speak fears abrupt beauty, there is always this feeling. Zhang Qiang throw away his gun. looking at the quiet and lovely Yaoyao sleeping position. a brother car carrying Doudong Jiang quickly fled in the direction of the hospital.
three is about the same as the age of the dragon sea dragon sea in the minds of the three elders in the existence of God.     The mentality of of inflammation Lieming white Xuanbing, the Su dream is followed by a look of stunned look. he gave the note ends up in his pocket and took out a tissue and wiped his forehead on the cold sweat.

Related articles:

 
Hi Aarthi,


Sorry for replying little late!


Here is the completed file:

https://hotfile.com/dl/171679524/a54bf6e/Copy_of_Data.xlsm.html


Assumption made:

1)At any given point of time, you may have 9 soap brands at input sheet (as you have filled 6 brands and kept 3 more rows blank)


2)Input sheet contains batch number (as you have mentioned in your above post).I have placed the batch number at A3 and placed the data from B3 to D15 (In case you want to make the rearrangement of this data position, please make the necessary 'cell reference changes' in the code as well; else leave as it is.


Work Done:


Coding part:


1)At C2 of report sheet I have created a drop down for batch number and I am storing the unique batch numbers (for each new data entry) from AA2 onwards in report sheet.


First part of the code is storing the unique batch numbers (for each new data entry) from AA2 onwards in report sheet


[Note: I have created a dynamic named range (BatchNo_Dyn) at Col AA of report sheet and pass the same in the 'source box' of data validation. To check the named range formula ctrl+F3 from keyboard and examine the formula in refer to box after selecting the named range)]


2) Next part of the code is just dynamically copying the input sheet data and store the same in Data-Saved sheet. At each click of the button (if you want to proceed further...I mean to say if you click 'yes' in the message box), data will be stored in Data-Saved sheet at an interval of 2 rows.


Formula part:


1)At Data-Saved, sheet from AA to AG, I have created formulae to calculate and or fetch all the parameter and or numbers based (matching) on batch number and finally passed these cell references in report sheet.


2)As of now, for example, we have three set of input data stored in Data-Saved sheet. The drop down at C3 of Report sheet showing three batch numbers. Selecting different batch number from drop down would change the numbers accordingly.


Now, request you to change the number/ data in input sheet and click the button to store the same. You should have another batch number in the drop down and selecting that batch number from drop down, the respective data should be populated.


Let me know if this is fine.


Regards,

Kaushik
 
Hi Kaushik,


Thank your for your efforts given and the workbook you have shared is working good. As I was little busy, I could not reply to you on time.


Really Thank you and I need to check it out with my friend, as I don't know about VB. And again I will inform it to you.


Regards,


Aarthi
 
Sue Arthi....


VB is doing a really very less job/simaple task here.


But the formula is doing the most of the work.


Anyways, if everything is fine also inform me in case you want me to explain the formula in detail, how it works...


Regards,

Kaushik
 
Really disappointing, I actually would go into the tea shop,fitzgerald, however,bard, say that it is indeed a very exotic place,nigel, Dating excellent Holy Land
 
Back
Top