I have work book, in which there are two sheets. Sheet1 consists of some data. Sheet2 concerned cells has to be filled from sheet1 based on the class selected from the drop down list.
Date wise sub totals (of previous date) of columns in sheet2 has to be automatically generated when a new date is entered in date column of sheet2.
Kindly help me in making this.
Last edited by sajeevjvarma; 06-27-2023 at 02:03 AM.
are you still using office 2007 ????
you have also posted in the VBA section , so i suspect you are after some VBA code - which i dont supply , however both parts can be done with a function
apolgies if not what you want, I'm not following exactly what info you are after - and made a few assumptions
i'm assuming now what the sheet names are - as not sheet1 or 2
also the data validation was looking at the wrong column
so i may have this all wrong
BUT i dont follow the next part of the question
would be useful to have expected results manually entered and why they are the resultsDate wise sub totals (of previous date) of columns in sheet2 has to be automatically generated when a new date is entered in date column of sheet2.
I suspect a SUMIF() could be used to calculate the totals based on a date change , maybe with an IF
=IF(A2<>A3,SUMIF(A2:A6,A2,L2:L6),"")
added to column N as i did not know where that was expected to be
i used in column H
=IFERROR(INDEX(ABSTRACT!C$2:C$14,MATCH($E2,ABSTRACT!$B$2:$B$14,0)),"")
and copied across to L
which matches the titles
in Abstract
TB WB/SK NB STAT TOTAL
Last edited by etaf; 06-27-2023 at 04:43 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks a lot Sir...
Let me clear that I need Only Excel related function not VBA Code.
"Date wise sub totals (of previous date) of columns in sheet2 has to be automatically generated when a new date is entered in date column of sheet2. "
For Ex: (According to the Worksheet provided by you)
A2 to A5 are the same date, When I change the Date in A6 Cell , all the data present in L2 to L5 should be summed up in the N5 cell.
Hope you get it now...
Last edited by sajeevjvarma; 06-28-2023 at 03:32 AM. Reason: Addition of Excel file
as asked
can you please provide what version of excel you are usingare you still using office 2007 ????
can you show more examples of the dates - does each date ONLY appear once .....
remove my assumed dates and add your dates as they would appear - ensure there are a few examples
in N2
assuming date entered once
Please Login or Register to view this content.
Last edited by JohnTopley; 06-28-2023 at 05:19 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Excel Version : 2007
does each date ONLY appear once ..... : Yes
Sheet is provided with few examples
Last edited by sajeevjvarma; 06-28-2023 at 06:20 AM. Reason: With Example data
Answer in post #5
Thank you so much Sir...
Kindly make it the way in the sample worksheet I have attached Sir.
i.e Sub Totals also for H,I,J,K,L,M & N columns
Last edited by sajeevjvarma; 06-29-2023 at 09:18 AM. Reason: Sub Totals also for H,I,J,K,L,M columns
You will need VBA to do this.
OR
in H4 and copy across to L
Formula:
Please Login or Register to view this content.
in N4
Formula:Please Login or Register to view this content.
the copy/paste H4:N4 to other "total" lines
you have different SUM for "Subtotal": which is it ?
Last edited by JohnTopley; 06-29-2023 at 12:43 PM.
Thank you very much Sir. Hope I am not troubling you Sir, Kindly Excuse...
Herewith sending the final sheet to solve.
Requirement:
Sub Totals of H to M column should be calculated automatically When a new Date is inserted in the Date column.
As shown in the sample sheet.
Last edited by sajeevjvarma; 06-29-2023 at 11:16 PM. Reason: Final Request
Thank you very much Sir. Hope I am not troubling you Sir, Kindly Excuse...
Herewith sending the final sheet to solve.
Requirement:
Sub Totals of H to M column should be calculated automatically When a new Date is inserted in the Date column.
As shown in the sample sheet.
Last edited by sajeevjvarma; 07-03-2023 at 07:58 AM. Reason: Final Request
Re: Help in preparing an Excel sheet for data management
Thank you very much Sir. Hope I am not troubling you Sir, Kindly Excuse...
Herewith sending the final sheet to solve.
Requirement:
Sub Totals of H to M column should be calculated automatically When a new Date is inserted in the Date column.
As shown in the sample sheet.
Last edited by sajeevjvarma; 07-04-2023 at 07:20 AM. Reason: Pls Help me out
Macro "Totals" does the summation but is not clear to me how you want this invoked: by entry of a date (plus data associated with the new date ???)Please Login or Register to view this content.
Macro "Totals" does the summation but is not clear to me how you want this invoked: by entry of a date (plus data associated with the new date ???)
Answer:
When a new Date is entered in Column A and pressed Enter Key, then data in the H to M column of the previous Date only should be summed up Sir
in sheet "Daily Acc"Please Login or Register to view this content.
Last edited by JohnTopley; 07-04-2023 at 10:45 AM.
Thank you so much Sir...
Is it possible only with excel functions without VBA ?
As I am unable to get the required result Sir
No .. VBA is required.
Ok Sir, Thank you very much for your help. Let me know the procedure to execute this in order to get the required result ...
Once again Thanks a lot
With High Regards...
S J Varma
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks