Cash Flow Forecast sheet not pulling from Categories?

:wave: Hello everyone :wave:

I have just installed the Retirement Planner and Cash Flow Forecast, but it seems to be glitching on me.

I’ve thoroughly read this help doc (or so I thought): Docs: Cash Flow Forecast. And it says:

But, in my case, the Cash Flow Forecast is not pulling any info from my Categories. Income, Expenses and Cash Flow are listed in rows 11-13 as $0.

Screen Shot 2023-05-04 at 1.37.59 am

Given that I’ve only just added these community add-ons, is there a way for me to fix this that doesn’t involve deleting things and starting again?

In the meantime, this is great for my self esteem because it means the Retirement Planner currently says I’ll be worth tens of millions of dollars at retirement. But that’s really because it thinks I’m investing 100% of my income and not spending any of it on expenses.

Hi @alyssa ,
Some things to check:

  • On your Categories sheet, do you have monthly budget amount set in the columns Jan thru Dec? Are there columns for the current year, Jan 2023 to Dec 2023?

  • Look at the hidden columns on the Cash Flow Forecast sheet. Does the Start Month in cell AF5 and then End Month in AF6 match 1/1/2023 and 12/1/2023 respectively?

  • In AG5 and AG6, does it list the column letter in your Categories sheet for budget for Jan 2023 and Dec 2023, respectively?

You shouldn’t have to delete things and start again on other sheets.
But the Cash Flow Forecast sheet pulls data from the Categories sheet. No other sheets. That is the only sheet it depends on.

If you are using a very old version of the Categories sheet, that doesn’t show budgets for each month, then you will have to update the Categories sheet.


Thanks @jono. Thanks for jumping in to help.

I checked everything you suggested, and the answer to all 3 is yes:

  • Yes, on the Categories sheet, I do have monthly budget amount set in the columns Jan thru Dec. There columns for the current year, Jan 2023 to Dec 2023.
  • Yes, in the hidden columns on the Cash Flow Forecast sheet, the Start Month in cell AF5 and then End Month in AF6 match 1/1/2023 and 12/1/2023 respectively.
  • Yes, in AG5 and AG6, it lists the column letter in my Categories sheet for budget for Jan 2023 and Dec 2023, respectively.

This is a new Tiller Foundation Template that I started for 2023, so all the sheets should be the current/recent versions, in theory.

In case this is relevant, @jono

I found a #REF error in one of the hidden cells of the Cash Flow Forecast speadsheet. This is what I see in cell Z2.

Sharing in case this is a clue… :woman_shrugging:

That is a big clue.

The formula in Z2, which should be:


It makes a list of all the categories that are not Hide status or Transfer type. It shouldn’t show a #REF error.

It uses formulas in AG24, AG25 and AG26 to get the Category, Type, and Hide From Report column ranges from your Category sheet.

My sheet shows
AG24 = Categories!A2:A
AG25 = Categories!C2:C
AG26 = Categories!D2:D

Does your sheet show that or something similar?


Mine is similar, but different @jono

The formula in Z2 currently reads:


I changed it to the one that you shared above, and still got a #REF error. It returned to the same old error message as before, and said:

Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 206. Actual: 1.

So then I went to check AG24-26 and mine we also different to what you listed above. It was a bit hard to understand because, in my sheet, AG25 has nothing listed - this cell has a heading in it, rather than a formula.

I’ll share a picture in the hopes this captures it. Ultimately, what I hope the picture shows is:
AG23 = Categories!C2:C
AG24 = Categories!D2:D
AG25 = “Current Calendar Year” heading
AG26 = Categories!F2:F

It doesn’t seem to say “Categories!A2:A” in any of the cells in column AG, from what I can see.

I’m pretty sure I found the problem.

You likely deleted a row in the Live Events section, which deleted important information in the hidden part of the cell.

What was on row 24 in the original template included the Column Lookup information for the Category column. Without that row, you will get the #REF and other errors.

To fix this, you need to re-install a clean version of the Cash Flow Forecast sheet.
If you put a lot of work into your current sheet, you might rename that one as Old Cash Flow sheet so you can save the data. But you will need to put in the data on the new sheet.

I believe there were some instructions about not deleting rows in the live events or any section of the sheet. I’ll add that to the doc file you linked to above.

It’s best just to overwrite data, because if you delete a row, you might delete needed formulas in the hidden part of the sheet.

Let us know how that works.

1 Like

Yep - you’re right. That fixed it @jono.

I obviously deleted something that I shouldn’t have. Thanks for all the detailed support.

I’m glad to hear we figured it out.