Switching from Google Sheets Array to Airtable

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.

1 Like


This is one google sheet

1 Like

Here’s the main database that includes projects and vendors


and the list of cost codes

1 Like

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 :heart: airtable

1 Like

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

If you want the template, I can send it in a private message. I don’t want to post the link here, because I’m about to use it on the On2Air site with a different link.

1 Like