Urgent help needed - auto updating bar graph to show current daily capacity

[nckwnchstr]

I'm desperate for help on an excel project I've been tasked to create, and I'm really struggling with figuring out how to create this product. I have a product that has several large charts of raw data, and then 4 graphs that are built off of that, and I need to add a few simple bar graphs to reflect key points on a daily basis out of this data. What I am attempting to do is create a basic 3 column chart that will have a Daily Capacity, COVID Capacity, and Surge Capacity bars, but I need them to auto populate based on the day they will correspond with in the chart. Is it possible to create a formula that says (for example) =today(=BM*correct row number) where if it is 21 July 2020 it will grab the number that is in BM2 and populate the graph, and if it is tomorrow, 22 July it will grab BM3, and so on? Am I way off base on thinking this is possible? I'm currently Deployed to the middle east and I'm on a tight deadline to try and get this product to my boss so that we can accurately project our housing capacity here on base.

 

[Toadstool]

Hi Nckwnchstr,

I'm not sure I fully understand but does this look like something?

Book1
	BL	BM	BN	BO	BP	BQ	BR	BS	BT
1	Date	Daily	Covid	Surge		Graph	Daily	Covid	Surge
2	15-Jul-20	44	25	14			11	6	3
3	16-Jul-20	22	12	7
4	17-Jul-20	11	6	3
5	18-Jul-20	0	0	0
6	19-Jul-20	88	51	30
7	20-Jul-20	44	25	14
8	21-Jul-20	11	6	3
9	22-Jul-20	0	0	0
10	23-Jul-20	88	51	30
11	24-Jul-20	88	51	30
12	25-Jul-20	66	38	22
13	26-Jul-20	44	25	14
14	27-Jul-20	22	12	7
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
BR2:BT2	BR2	=INDEX(BM:BM,MATCH(TODAY(),$BL:$BL,0))

So you base the graph on BR2:BT2 and populate those cells based on the date.

 

[nckwnchstr]

yes,
I'm trying to see if i can get it to automatically update to the next day without having to change the formula or anything, so if its the 17th, it shows that, and on the 18th it will automatically change to 0 0 0, and the 19th it would automatically change to 88, 51, 30

 

[Toadstool]

yes,
I'm trying to see if i can get it to automatically update to the next day without having to change the formula or anything, so if its the 17th, it shows that, and on the 18th it will automatically change to 0 0 0, and the 19th it would automatically change to 88, 51, 30

Yes, that's what the formula does.

Here's another version which will be easier to test as it takes the date from cell BW1, so after testing you can just change the contents of BW1 to =TODAY()

nckwnchstr.xlsx
	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX
1	Date	Daily	Covid	Surge		Graph	Daily	Covid	Surge		Date=	23-Jul-20
2	15-Jul-20	44	25	14			88	51	30
3	16-Jul-20	22	12	7
4	17-Jul-20	11	6	3
5	18-Jul-20	0	0	0
6	19-Jul-20	88	51	30
7	20-Jul-20	44	25	14
8	21-Jul-20	11	6	3
9	22-Jul-20	0	0	0
10	23-Jul-20	88	51	30
11	24-Jul-20	88	51	30
12	25-Jul-20	66	38	22
13	26-Jul-20	44	25	14
14	27-Jul-20	22	12	7
15
16
Sheet1 (2)

Cell Formulas
Range	Formula
BR2:BT2	BR2	=INDEX(BM:BM,MATCH($BW$1,$BL:$BL,0))

 

[nckwnchstr]

AWESOME!!!

You are a life saver! Thank you very much!!

 

[Toadstool]

AWESOME!!!

You are a life saver! Thank you very much!!

You're welcome! ...and thank you for your service.