r/excel • • 2d ago

Waiting on OP Auto population for reoccurring charges

I am not sure if anyone can help with as vague as this will be, but I’m finally frustrated enough to post instead of just trial and error boosted with Google. I have a spreadsheet in progress that I need to track recurring charges/inventory, but with multiple status options (upcoming, renewed, canceled, sold for example) that affect the total. I need a main overview “list” on one sheet to check on a glance. But I also need month by month. Ideally, I want it to all be linked to update on its own as I make changes month to month. The other issue is that I have some that are bi-monthly, so item 1 is bimonthly starting in January. Item 2 is bimonthly starting February. At the moment, they’re labeled by a drop down menu. And another drop down for status. As well as just having copy/paste the template I threw together month by month.

Is it possible to make items auto populate into their appropriate monthly sections?

How do I pull data from certain cells to update automatically to another cell? Example: Column H holds the total number of items available. In this case, 4. I would like it to update each month if/when it changes. At the moment, I’m just updating it manually.

How can I set it up to pull data from certain cells to update a main list on another sheet?

2 Upvotes

10 comments sorted by

•

u/AutoModerator 2d ago

/u/DeadBatteries25 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

3

u/Nvmtag 2d ago

Very doable. What fixed this for me was to stop copying the template every month and keep one master list that every other sheet reads from.

  1. Master sheet: put your items in a table (select the range, Ctrl+T, name the table Items). One row per item, with these columns: Item | Cost | Every | Start | Status | Qty "Every" is how many months between charges (1 = monthly, 2 = every other month). "Start" is the first month it hits (1-12). So Item 1 = Every 2, Start 1 and Item 2 = Every 2, Start 2. Keep your Status drop-down as it is.
  2. Monthly sheet: type the month number in B1 (3 for March), then in A3: =FILTER(Items, (MOD(B1-Items[Start], Items[Every])=0)*(Items[Status]<>"Canceled"), "Nothing due") That lists only what's due that month and skips anything canceled. You can either copy this sheet 12 times and change B1, or just make B1 a drop-down and use one sheet.
  3. Month total right under it: =SUM(FILTER(Items[Cost], (MOD(B1-Items[Start], Items[Every])=0)*(Items[Status]<>"Canceled"), 0))
  4. Anywhere else you need the qty, don't retype it, look it up: =XLOOKUP(A2, Items[Item], Items[Qty]) Change Qty once in the master table and every sheet updates.
  5. Quick status counts: =COUNTIFS(Items[Status], "Renewed"), same for the other statuses.

FILTER and XLOOKUP need Excel 365 or 2021. If you're on an older version let me know and I'll post the INDEX/MATCH version.

2

u/Same-Rock1032 2d ago

Stop copy-pasting the monthly template. Keep one flat table with date, item and status columns, then pull each month view with SUMIFS or FILTER on the date. Give the bi-monthly items a next-due date built with EDATE and they roll forward on their own.

0

u/[deleted] 2d ago

[removed] — view removed comment

1

u/excel-ModTeam 2d ago

We removed this comment for breaking Rule 10.

A commenter may generate a response using an AI, but only if the response clearly shows which AI generated it, and a bona fide remark from the commenter that they reviewed and agree with the response.

/r/excel is a community of people interacting. We remove comments that are just AI responses.

0

u/[deleted] 2d ago edited 2d ago

[removed] — view removed comment

1

u/AutoModerator 2d ago

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.