Switching from Google Sheets Array to Airtable

[Wesley_Bascom1]

Hi,

We currently use google sheets to keep track of project budgets (this is fed data from a complex set of estimating/budgeting sheets plus quickbooks. The quickbooks part is manual).

I want to be able to bring all our project budgets into one sheet.

We already use airtable to track subcontractor costs, as well as other items by project, so we have the database of projects well developed.

Does anyone have any ideas for how we might now associate this estimated vs actual cost data? We could even add an actual revenue line. I’m imaging tracking this data across projects by cost code or drilling into each cost code.

It’s important that:
-We can view it in a compact interface
-Data entry is no more difficult
-Ideally we get “snapshots” over time. (I have created a pay schedule before when tracking employee hours so the idea of advancing ‘schedules’ is something I’m familiar with)

If it’s too difficult, I’ll probably just use IMPORTRANGE in google sheets to create a summary view… womp womp.

[Wesley_Bascom1]

This is one google sheet

[Wesley_Bascom1]

Here’s the main database that includes projects and vendors

and the list of cost codes

[Wesley_Bascom1]

And some fun images from the interface I made in the receiving database - completely unconnected, but what really brought me back to Airtable after so many years away

I airtable

[Hannah_Wiginton]

Hey @Wesley_Bascom1,

I actually just created a Project Budget template last week that is very similar to what you’ve posted. It calculates different costs per line item. It also calculates a budget vs actual.

Though it’s difficult to know exactly what your calculations are in your spreadsheet, maybe it’s a nice start.

If you run into some limitations while converting everything, you might be able to use our On2Air Actions app for a couple of things.

1. Airtable can’t calculate between records like Sheets can. We do have a VLOOKUP-similar function that can help with this and also some Array functions in the app.

2. For ‘snapshots’, we have a Field Diff Log function in the Actions app the keeps a running log of changes to a field. Plus, Actions has 60+ other helpful functions.

3. If you want custom record views, try our Amplify app

On2Air | Accomplish Anything with Airtable – 13 Jan 22

Airtable Project Budget Template | On2Air

Use this Project Budget Airtable Template to create budgets and reports for your projects. It includes a Budget table with calculations, Clients table to link to a Budget, and Reports table to generate full budget report documents. Use the On2Air...