Ad-Free CAclubindia and Daily Updates on your mobile. Subscribe Now!

Require Excel Function which can solve my query

C A tejal (Practising CA) (108 Points)

22 May 2010

Hello Friends...

Herewith I have attached one stock excel work sheet in which sheet named as 'oct' shows a day wise detail regarding opening, purchase, sale and closing of 30 items. I have seperate sheet for seperate month. Now I required detail as shown in sheet 1

Is there any single function in the excel by which I can derive my result ?

Just by copy and paste of such function i can get my result in that formate?

Thanking You.

Reply
Follow
Share

12 Replies

Krishan (Service) (633 Points)
Replied 22 May 2010

1 Like

Upgrade to CAclubindia Pro. Subscribe Now!

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi) (14487 Points)
Replied 23 May 2010

Please find attached file matter solved by Vlookup commond

Attached File : 23 copy of 52 stock sheet.xls downloaded: 295 times

1 Like

Krishan (Service) (633 Points)
Replied 23 May 2010

another file is with me ...

bt i m unable to attach here...

you may PM ur mail id to me...

Exshail (Software) (1576 Points)
Replied 23 May 2010

Without any function formula, you can select all purchase range & copy, goto your destination cell, paste special, tickmark transpose & click on ok. Another way is to make simple database of inventory & use of Pivottable for various report.

BTW for maintaining inventory I preferred readymade\customize software which give better report (as per fifo\lifo etc) better then having reports in Excel or Access. There are also now available many free version of Accounting software with Inventory. One of the best is Free version of Busy UL you can download from

https://www.busy.in/index.php?p=fas

Regards,

Shailesh

https://shahshaileshs.web.officelive.com/

Free Addins Office Menu-2003 for Office-2007
https://shahshaileshs.web.officelive.com/MenuAddins.aspx

Free Exshail Classic Menu for Excel-2007
https://shahshaileshs.web.officelive.com/Exshail_Classic_Menu.aspx

1 Like

Vinod Kothari (Chief) (1269 Points)
Replied 24 May 2010

you are perhaps wanting to add numbers in a particular cell across different sheets. This is called 3-D reference and you can do such summation by entering the sum formula, hold shift key, and click on all the sheets whose cells you want to add

Upgrade to CAclubindia Pro. Subscribe Now!

CA. Dashrath Maheshwari (TaXpert) (15103 Points)
Replied 24 May 2010

Solution is sent at your mail ID

Exshail (Software) (1576 Points)
Replied 25 May 2010

It is very strange that this forum is for sharing knowledge however the forum moderators are not willing to share the knowledge. I remember my one of the post about sharing good info was also disappear whithin few hours after posting it. Dir link was also not posted many times.

Shailesh

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi) (14487 Points)
Replied 25 May 2010

Dear Shailesh Ji sometime a file send by us not useable or not working in proper manner or not use full to other member due to protection so its not qualifying to display in this forum. insted of these thigs you can attached your file in this forum by above way these files are attached in this matter. it will send to all member mail ID in same day who is registered free mail/news/update.

we hope you will be continue with this forum to share your valuable knowledge.

Thanking You

Ram Avtar Singh

Exshail (Software) (1576 Points)
Replied 25 May 2010

Thanks Ram Avtarji for your response. I agree with your opinion. But I see many files attached in files section itself are protected and works of others.

I had posted a website link to official download free version of useful professional softwares which was disappear within few hours after posting.

So many users feels that sharing knowledge is one way directional and avoid to participate further otherwise forum moderateres will post solution here & not privatley email.

Thanks & regards,

Shailesh

CA CS CIMA Prakash Somani (Landmark Group) (23502 Points)
Replied 25 May 2010

ust follow a simple process

1. Copy the selected data

2. Go to special paste

3. Check on Transpose

4. Click Ok.

Now you will see your row data in columns and column data in rows in sheet 3 of attached file.

5. Now filter on column C on "Cl" and you can manually put Item 1, item 2 from top to bottom.

Attached File : 22 stock sheet.xls downloaded: 142 times

Upgrade to CAclubindia Pro. Subscribe Now!

CA. Dashrath Maheshwari (TaXpert) (15103 Points)
Replied 26 May 2010

Dear Shailesh ji, and all other forum users,

I have got personalized mail from CA Tejal for the solution, and therefore I have replied with the solution via "REPLY BUTTON" there.

If you all want the solution, its herewith attached.

Originally posted by : Shailesh

Thanks Ram Avtarji for your response. I agree with your opinion. But I see many files attached in files section itself are protected and works of others.

I had posted a website link to official download free version of useful professional softwares which was disappear within few hours after posting.

So many users feels that sharing knowledge is one way directional and avoid to participate further otherwise forum moderateres will post solution here & not privatley email.

Thanks & regards,

Shailesh

We don't have any personal interest or desire to moderate your forums, but if any forum thread or reply is out of scope (Not qualifying as per rules), or if we get members' negative feedbacks then only we used to modify or remove the forums.

and.. its needed in this type of professional plateform..

I think, maximum members will be agree,

Yours in service,

Attached File : 38 new.xls downloaded: 137 times