Make Cells Appear Based off Matching Text

[wherdzik]

Just reaching out to see if anyone has a good solution. Each month I will be pulling in data and calling the tab names January, February, March, etc.. In the Summary Tab I will use the drop down to sect the month I am working in which will be cell A5. Once I select the month (A5), I would like some sort of formula which would list all dates from the tab that the month is selected. So in the case of November I would like to list all these dates in column A8:A16. Then In column B i would like to list the corresponding values. Is this possible? and did I do a good job explaining this? Please see attached file


Place in column A: Place in column B:
2020-11-02 Total 24.26
2020-11-09 Total 26.04
2020-11-16 Total 27.97
2020-11-20 Total 2.11
2020-11-21 Total 0.00
2020-11-23 Total 24.13
2020-11-30 Total 23.62



Thank you.

[alansidman]

With Power Query, I created a parameter query that does what you are asking for. You will need to make sure that each tab is formatted exactly the same with the same headers exactly. Additionally, each tab must be converted to a Table with headers and named for the month in question.

Look at the attached. Make your selection in the data validation, then select Refresh All.

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.

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

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

[Bo_Ry]

Please try at
A8
=IFERROR(INDEX(INDIRECT($A$5&"!$A$6:$A$99"),AGGREGATE(15,6,ROW($A$6:$A$99)/(LEN(INDIRECT($A$5&"!$A$6:$A$99"))>14),ROWS(A$8:A8))-5),"")

B8
=IFERROR(INDEX(INDIRECT($A$5&"!$E$6:$E$99"),AGGREGATE(15,6,ROW($A$6:$A$99)/(LEN(INDIRECT($A$5&"!$A$6:$A$99"))>14),ROWS(A$8:A8))-5),"")

[wherdzik]

Fantastic exactly what I was looking for. Very much appreciated it. I love this forum and the people on here. It is amazing that you guys want to help other people with nothing in return for this..just out of the reason that you want to help.

Thank you to you both.

[wherdzik]

Can you help me understand how this formula works? How does it know to look for the total? Thank you.

[alansidman]

Can you help me understand how this formula works? How does it know to look for the total? Thank you.

Who is this message meant for?