Automatically change a cells reference cell at the start of every month?

I'm working on a budget/tracking sheet specifically for myself.

I have a row for groceries which is 2 columns, the name and the $ amount.

The dollar amount references another sheet where there are 12 rows, 1 for each month, and the total amount I've spent that month.


Is there any formula or function that would automatically change the dollar amounts reference to the next month?

for instance, groceries for feb. gets the value from sheet2's January column, and when the month changes to march it should pull the value from sheet2's feb.
Can this be doneautomatically?

If you change the months on Sheet2 to dates (1st Jan 2023, 1st Feb 2023 etc.) then you could use an Index/Match formula to reutrn the desired result.

Have a look at the attached. I've changed them to dates, but formatted it to look like it did before, then used the formula below on Sheet1

Formula:

Please Login or Register  to view this content.

BSB

Awesome thanks, I'll play around with that a bit, it's working and starting to make a bit of sense the more I play with the formula

In plain English it says "Find the last day of the month that is two months previous to today's date then add 1 to it (that gives you the first day of the month before the current date). Then find that date in column A of Sheet2 and return the corresponding value from the same row but in column B."

Make any more sense now ?

BSB

That explanation clarifies it, and it seems to be working perfectly as the cell changed when the month did.

Originally Posted by DarkSoul

That explanation clarifies it, and it seems to be working perfectly as the cell changed when the month did.

Glad I could help

BSB

Originally Posted by JohnTopley

Please update your profile to include Location and Excel version (Os 365 + release, 2021 etc) as both are used when responding to queries

updated.....
Any way to change a dumbass user name that was chosen in the height of a persons 2008 20-something-year-old-edgelord phase?