Creating a Court diary as per Excel data

[VDS]

Dear All experts,

Thank you very much for the support provided to me earlier by this forum which helped a lot in learning excel.

I attach herewith an excel file containing two worksheets. It is the format of cases handled by a law firm. Here, cases are listed before the court date wise.

For checking the datewise list of cases I have to sort the Column "DOH" and check the particular date to display entire records. There may be more than one case. For eg, if I am checking the date of 11-07-2023, There are 7 cases and like that.

Sheet 1 named "For the month of July" is the master data
Sheet 2 named "Daily cause list"

I have to make separate excel worksheet to show total number of cases for each day.

In the second worksheet, Just put the any date in the top and based on that, the entire record will be shown with formula. The Columns in the sheet " : "Item No", "Person Attended" and "NDOH" will be filled manually on daily basis.

What I request you to get the variable data of a particular date with formula.

There is no merging. Any formula can be welcome. Vlookup or index /math or anything

VDS

 

[AlanSidman]

Attached is a Power Query Parameter solution. Select the date you wish the data for and then select the VBA Refresh Button and the information will appear.

 

[VDS]

Sir, This is showing error. How I can correct this.
Refresh query option is not updating.
I am not expert in VB or macro

Can you suggest any formula with array commands ? something like =iferror(small(match), etc


VDS

 

[vletm]

VDS​

Could this work for You?
Instructions:
# DAILY CAUSE LIST-sheet's headers have to be same as FOR THE MONTH OF month
# while edit FOR THE MONTH OF month-sheet
... take care that cell T1 font is normal
# while select data to DAILY CAUSE LIST-sheet,
... take care that FOR THE MONTH OF month-sheet's cell T1 font is bold
# above 'take care' means - select cell T1 to swap font normal <> bold
# Select any date from FOR THE MONTH OF month-sheet's T-column to get data to DAILY CAUSE LIST-sheet

 

[bosco_yip]

Another option of a formula way.

This is a simply formula way, with a helper column + Index & Match formula:

1] In "FOR THE MONTH OF JULY" helper colum A2, enter formula and copied down:

=IF(E2="","",IF(T2='DAILY CAUSE LIST'!H$1,MAX(AC$1:AC1)+1,""))

2] In "DAILY CAUSE LIST" range A6:J6, sheet's headers have to be same as "FOR THE MONTH OF JULY" sheet headers

3] In "Enter case criteria" cell H1, prepare a "Validation dropdown list" with unique DOHs

Then,

4] In A7, formula copied across right and down:

=IFERROR(INDEX('FOR THE MONTH OF JULY'!$E$2:$AA$50,MATCH(ROW($A1),'FOR THE MONTH OF JULY'!$AC$2:$AC$50,0),MATCH(A$6,'FOR THE MONTH OF JULY'!$E$1:$AA$1,0)),"")

5] Please see attachment.

 

[VDS]

This seems to be okay. But how to apply the function on the original data ? Shall I copy paste the entire data on this file? Have you applied any macro on this attached excel file ?

 

[vletm]

VDS​

About Your last sentence Have you applied any macro on this attached excel file ?
Did You refer above (#6 reply) to my #4 reply? Could this work for You?
... if 'yes' then ...
# It should work.
# Copy needed code to Your original file
OR
# Copy Your data to my sent file and use it.
# Yes, there are some code.

 

[VDS]

Another option of a formula way.

This is a simply formula way, with a helper column + Index & Match formula:

1] In "FOR THE MONTH OF JULY" helper colum A2, enter formula and copied down:

=IF(E2="","",IF(T2='DAILY CAUSE LIST'!H$1,MAX(AC$1:AC1)+1,""))

2] In "DAILY CAUSE LIST" range A6:J6, sheet's headers have to be same as "FOR THE MONTH OF JULY" sheet headers

3] In "Enter case criteria" cell H1, prepare a "Validation dropdown list" with unique DOHs

Then,

4] In A7, formula copied across right and down:

=IFERROR(INDEX('FOR THE MONTH OF JULY'!$E$2:$AA$50,MATCH(ROW($A1),'FOR THE MONTH OF JULY'!$AC$2:$AC$50,0),MATCH(A$6,'FOR THE MONTH OF JULY'!$E$1:$AA$1,0)),"")

5] Please see attachment.

View attachment 84637

@ Received the message and sample file. Let me work on this and will revert soon

 

[AlanSidman]

Did you see the macro in the file that you need to apply to your actual file. Click on Alt +F11. Works in my copy of the file.