Getting the data for a particular date in a separate worksheet.

[VDS]

Dear All experts,

I am attaching herewith an excel file of two worksheets.

Sheet 1 is named Master Data and Sheet No.2 is named Daily Report.
Output is required in the “Daily Report” worksheet for a particular day the same date is entered manually by the user. The dates in the Master Data file is changed when that particular date is over.

The query has to be entered in the Cell E2 of “Daily Report”. The process should be looking through entire data from “Master Data” based on query and display as per format in “Daily Report”. and don’t want to disturb the Master Data worksheet by any other commands like sorting, conditional formatting, etc.

I am using excel in the Office Professional Plus 2010 version.

Any function like Vlookup, Index+match or any of the array commands most welcome.

Thank you all in advance.

Regards
VDS

 

[AlanSidman]

Create a parameter query in Power Query as shown in the attached.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NDOH", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([NDOH] =Table3))
in
    #"Filtered Rows"

 

[VDS]

Thanks for reply.

Is it a macro ? Some error message is coming.

pls see the screenshot.

I am using excel in the Office Professional Plus 2010 version.

VDS

 

[vletm]

VDS​

Why do Your sample expected result has only two rows with 13/Dec/2025?
... missing row 6 from MASTER DATA or how?

Could You use Auto-Filter -option with MASTER DATA-sheet to get Your 'daily report'?
... without Daily Report-sheet

 

[VDS]

VDS​

Why do Your sample expected result has only two rows with 13/Dec/2025?
... missing row 6 from MASTER DATA or how?- Answer. This is a sample report not generated through function or formula.

Could You use Auto-Filter -option with MASTER DATA-sheet to get Your 'daily report'?
... without Daily Report-sheet: Answer: There are few templates to be added in the Daily report sheet and later on to be generated as PDF.

Hence both worksheets are required. In fact there are so many live data to be entered in the excel file. Once any formula is ready, I am so happy to use it.

Regards
VDS

 

[vletm]

Even a sample report should be valid.

Few samples:

Alternative to FILTER function in Excel 2010?

One one sheet I have a large list of doctors, and against each doctor I have listed their secretary. Some of the doctors share the same secretary too. On a separate sheet, if I choose the secretary, I want to be able to return which doctor(s) that secretary works for. I got this working...

www.mrexcel.com

EXCEL function FILTER for Excel 2007 to 2019

FILTER function for Excel 2007 to 2019, with examples, free download. Filtering with wildcards.

hermann-baum.de

Scroll down until
>> Filtering without FILTER function

 

[VDS]

Let me try and confirm

VDS

 

[vletm]

... or something like this sample
usage: modify E2 cells date

 

[VDS]

It is good one. So Nice of you.

I will try with live data and put more queries.

VDS

 

[AlanSidman]

Is it a macro ? Some error message is coming.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions