Help...Automatic selection of Dates for the current Month

[Dinesh_Excel]

Hello All,

I have a data set with one of the columns ranging different dates and the other two columns has some numbers.

Do we have any formula using date functions where the last days of the previous month and the current months ( starting from 1st of every month to today's Date) should reflect in date date set.

Appreciate your help. Attached file for your reference.

Thank you



Example as below:

Base data set:



Output set :

 

[GraH - Guido]

try this one =EOMONTH(TODAY(),-1)
Added some formula to "automate" the full output table.

 

[bosco_yip]

Maybe,

In I5, copied across to K5 and all copied down :

=IFERROR(INDEX(B$4:B$24,MATCH(EOMONTH(TODAY(),-1)+ROWS($1:1)-1,$B$4:$B$24,0)),"")

Regards
Bosco

 

[Dinesh_Excel]

Thank you Bosco and Gruh...I really appreciate your help here.

While I was trying to plot a graph , can the graph show dynamically last date of previous month data and current months till date data as per the below graph.

The challenge here is the additional dates which will be added in coming days should automatically plot without manually we going and changing the range.

The below graph shows bars where future dates has no data in it.

Please help.

 

[GraH - Guido]

The graph in your uploaded excel seems to auto grow. I manually added some fake data as of 17/03/2018 and it showed up.
That is not what you ask?

 

[rahulshewale1]

Hii @Dinesh_Excel ,


See if it is ok ?



Regard
Rahul shewale

 

[Dinesh_Excel]

Hi Gruh/Rahul,

May be I did not put it in a right way.let me try again in explaining you in detail...

The data set keeps on growing as the days advances and the output data captures only last month data along with current month's data till to date(MTD).


@ Rahul seems to have captured it partailly right however when the "Data set"
expands along with the output file it should automatically capture only the data which is exists in" Output File" rest should be ignored.


Below graph is plotting only data till March 10th any advances made in "Data set" are not captured automatically in the chart .

challenge : every time we need to manually change the range for the plotted graph.

Hope I clarified Attached file for your reference.
Thank you

 

[rahulshewale1]

Hii @Dinesh_Excel,


PFA revised sheet.


Regard
Rahul shewale

 

[Dinesh_Excel]

Thanks Rahul its working now...howwver can you please help me in understanding the way it worked for you using name manager.

 

[rahulshewale1]

Hii @Dinesh_Excel ,


I used Name manager for Dynamic range Chart

Date name Range=OFFSET(Sheet1!$G$4,,,MATCH(0,Sheet1!$G$5:$G$99,0),1)
Items Range =OFFSET(Sheet1!$H$4,,,MATCH(0,Sheet1!$G$5:$G$99,0)-1,1)
Occurrence =OFFSET(Sheet1!$I$4,,,MATCH(0,Sheet1!$G$5:$G$99,0),1)

https://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

https://excelchamps.com/blog/dynamic-chart-range/



Regard
Rahul shewale

 

[Dinesh_Excel]

Thanks Rahul for making me to learn...Appreciate your help and Gruh.