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

nckwnchstr

New Member
Joined
Jul 21, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,152
Office Version
  1. 2016
Platform
  1. Windows
Hi Nckwnchstr,

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

Book1
BLBMBNBOBPBQBRBSBT
1DateDailyCovidSurgeGraphDailyCovidSurge
215-Jul-204425141163
316-Jul-2022127
417-Jul-201163
518-Jul-20000
619-Jul-20885130
720-Jul-20442514
821-Jul-201163
922-Jul-20000
1023-Jul-20885130
1124-Jul-20885130
1225-Jul-20663822
1326-Jul-20442514
1427-Jul-2022127
15
16
17
18
Sheet1
Cell Formulas
RangeFormula
BR2:BT2BR2=INDEX(BM:BM,MATCH(TODAY(),$BL:$BL,0))


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

nckwnchstr

New Member
Joined
Jul 21, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,152
Office Version
  1. 2016
Platform
  1. Windows
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
BLBMBNBOBPBQBRBSBTBUBVBWBX
1DateDailyCovidSurgeGraphDailyCovidSurgeDate=23-Jul-20
215-Jul-20442514885130
316-Jul-2022127
417-Jul-201163
518-Jul-20000
619-Jul-20885130
720-Jul-20442514
821-Jul-201163
922-Jul-20000
1023-Jul-20885130
1124-Jul-20885130
1225-Jul-20663822
1326-Jul-20442514
1427-Jul-2022127
15
16
Sheet1 (2)
Cell Formulas
RangeFormula
BR2:BT2BR2=INDEX(BM:BM,MATCH($BW$1,$BL:$BL,0))
 

Forum statistics

Threads
1,147,477
Messages
5,741,362
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top