Army Ammo Tracker

I'm creating a spreadsheet to track our ammunition useage over the course of a fiscal year (OCT-SEP), and I would like my 'master' tab to always show (amongst other data) the next 3 months' worth of forecasted ammo (labeled "Locked Out") and the 4 month (labeled "Working").

Everything on the Master tab is already using formulas to pull data off of the 'month' tabs (OCT-SEP); I'm curious if there is a way, instead of having to manually input the next 3 months, if someone knows how to write a formula that will populate those cells based off knowing the current month. Then, knowing the current month, it will automatically pull the ammo data from the appropriate month's cells (ie, APR, MAY, etc) including putting the appropriate month's titles in the cells on the Master tab (ie, "APR", "MAY", "JUN", and "JUL").

I've attached a Word document with a couple of screenshots of the file. Any help is much appreicated.

You can do this with user defined functions. I've writen one for one month after now, you should be able to produce similar ones for the other months.

Please Login or Register  to view this content.

Put this on a VBA module sheet.

It will also help to add the following code to the tab for your master sheet in the VBA editor....

Please Login or Register  to view this content.

...so that the sheet will auto-refresh whenever you select it.

have figured out how to make your excel sheet? because i am trying to create something similar