Do you create a new sheet each year?

I am at a loss whether to create a new sheet for each year or to keep the sheet running year after year. These are pretty large spreadsheets. Will it even be able to run with many years of data building up?

2 Likes

Our Foundation Template can handle multiple years of data, so when a new year begins, you can add that years data to your spreadsheet. Here’s more on that:

1 Like

Thanks for the info @warren. I have 1.5 years of data in my sheet right now. I know that it is a capability but my question is more around performance impact. Currently, my sheet is somewhat slow (compared to other Gsheets) and bogs down my computer after some time. I can’t imagine what the performance will be like with 3, 4, 5 years of data :flushed:

Got it!! Here is a good discussion on this topic.

Speaking from personal experience, I have two Tiller Money spreadsheets I use all the time. One if for our family, and in that one I keep a list of transactions that is many years long. I keep that sheet running fast with 17,000 transactions by keeping processor intensive reports to a minimum. For example, I don’t let extra pivot tables sit around collecting dust if I’m not using them.

In the other spreadsheet we use to manage the finances for Tiller Money the company, each spring (a few months into the new year) we make a duplicate spreadsheet, for example called “2019 Archive”, and use that to store last year’s data. Then in the live spreadsheet we prune prior year data. That keeps it small and fast.

Two approaches, both work.

2 Likes

Hi @hbwilliams22,
Similar to @peter, I use both approaches. But then I throw in a 3rd.

For family transactions, I make a new Spreadsheet each year.

For business expenses, I just have one Spreadsheet with multiple years.

But for the family transactions, I also make a New Spreadsheet where each tab is an import of the Yearly Transactions sheet. I use the IMPORTRANGE() function to pull in the Transactions report for every year.

More info about IMPORTRANGE() here:
https://support.google.com/docs/answer/3093340?hl=en

Then I have a Everything sheet which pulls in all the Yearly transactions sheets into one sheet using the formula:
={'2020'!A:K;'2019'!A2:K;'2018'!A2:K;'2017'!A2:K}

This makes multi-year reports or searches much easier.

I only pull in Columns A thru K in each years Transactions sheet.

This sheet gives me the best of both worlds and keep performance fast.

Jon

1 Like

@jono I am trying to replicate how you imported multiple tabs. can you share the entire formula/function? Perhaps someone at Tiller can clarify @heather

Hi @nataliapinzon,
To import a Tiller Transactions sheet into a new Spreadsheet, use the IMPORTRANGE function.

In the new Spreadsheet, put this formula in cell A1:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Transactions!A:K")

Replace the abcd123… with your spreadsheet url value (it’s a long series of numbers and letters) from your Transactions sheet. You can find it after the d/ in your spreadsheet URL, which should start something like this: https://docs.google.com/spreadsheets/d/

In my example above, I’m importing columns A thru K. But you can change those letters to import any range of columns you want.

When you first enter the IMPORTRANGE() function, Google will ask you to connect the sheets before it will work. Once you connect the sheets, you don’t need to do that again.

I make the sheet name for each year’s import the name of the year.
Then, in a new sheet in that new spreadsheet, I can get one sheet with all the transactions by using:
={‘2020’!A:K;‘2019’!A2:K;‘2018’!A2:K;‘2017’!A2:K}
in cell A1.

Did that help you understand it?

Jon

Yes it did, I understand the concept now. I tried but but I got a formula parse error. heres what I did.

  1. Created a new spreadsheet and used import range to import my transactions sheet, successfully linked the sheets
  2. in that spreadsheet, I created a new tab and in A1 I placed the following formula to test: ={‘2019’!A:K} (the sheet that I imported from has a tabbed titled “2019”)
  3. That did not work so I tried using the formula in my original (source) sheet, and I still get the “formula parse error”. I tried moving the quotes and nothing.

Thank you for your help in troubleshooting this!

What happens if you remove the single quote in your step 2:

={2019!A:K}

Single quotes are needed if a sheet name has a space on it. I use them in examples in case people have a space.

Single quotes that are open and closing quotes (if that’s possible) don’t work in Sheets. Sometimes programs like word put them in. You might have non-standard quotes. So, see if removing them helps.

Does your range in sheet 2019 go from A to K? If not, that could be an issue.

I’m not sure what your comment in 3. means.