How Excel changed my life

In the wise words of the virgin mary, “come again?”,

Back to my projects full time, and have been leaning back on stuff i once learnt in school and my brain felt was not important enough to retain at one most crucial of moments (fml). Excel! This program is just awesome, if utilized properly. I do all my invoicing with it, client lists, project names, info etc. I can keep an account of billing aswell. I sound like an absolute geek but the truth is, the simplest tools are the most useful and excel is amazing.

You might be thinking, big whoop, but i know a lot of my compadres who don’t know how to use it so here’s just one tool that revolutionzed my excel power. It did, however, take me a bit of time setting everything up, but here’s the result.

The cell name doesnt change its “A2” ref. It’s added info.

Here is a list of client info pertinent to your needs. You can make a shit ton of these, but well, in this instance, less is more… Notice the ID at the front. This ID’s cell can be given a name! YES! I didnt know it either but it gives them a reference that can be used to populate a list! So all cells NAMED ID for ex., will generate a list that can be used elsewhere.

This number is going to allow me to link all the info along the line to THAT number. This allows me to be able to pull data up from other sources into, an invoice let’s say, via, that ID number (nota; this number can be hidden in forms by using color, and doesn’t need to be numerical, a list of invoice numbers for ex. is perfectly doable) This is huge for me because i set up several sheets that populate my billing and invoicing. i don’t spend hours anymore trying to desperately keep my accounts up to date. I created a database in excel!!

To create the lists i used the data validate function ON the cell i want to format. I personally used them at the top of my invoices.

I then drop down the menu to the list function and add “=the name of the cells that you want to list. In my case it was “ID“. This links the list to the ID selected. By using a bit of VLOOKUP magic (look it up) i can now effortlessly create invoices and keep a track of everything!

On my invoice’s i can select an ID number or even a name that populates the other fields needed. Personally, i have 2 “databases”. Client info and project info.

I won’t show the whole thing but needless to say i have fallen in love all over again. I have also been tinkering with dynamo and integrating quantitatives in excel pulled from Revit. You can obviously do cost and quantitative analysis in Revit but the data isn’t as versatile as excel. Once again, the VLOOKUP function rocks, and i can import Revit parameter info into excel that i can manipulate on a different page. Meaning i can re-use the same import/export template for all my pricing studies!

Pay for Oracle? Pay some muppet for a custom software? Learn yet another useless program?

bollocks. Excel mate.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.