Trying to make Excel do stuff it was never designed to do

Anyone here got some serious Excel knowledge? I’ve been building a bit of a frankedsheet, and really could do with some pointers on it. Essentially it’s for recipe management, costings and batch production logging. I cannot find a specific piece of software that covers those three bases sadly. The batch logging in particular just seems to land me in large scale industrial production level software, which is way out of my league.

Things I really could do with it handling:

Recipe layouts have headers and footers of consistent information. Ideally I’d like a master template I can update that will update all the instances I’m using rather than doing it manually.

Recipes have a variable length ingredients list, and all the header / footer formula need to take that into account.

When I do batch production costings, I need to be able to have my price list reflect the current prices, and have older batch logs storing the prices as it was at the time (plus a few other bits of info possibly).

Am I on a losing battle?

There’s a bit of software called ‘open bom’ which may do what you need. It’s not free but I think you can trial it for nothing . It’s aimed at manufacturing but it should handle what you want

Just having a look at that. Lots of terminology to get my head around!

My knowledge of XL is truly pish. But, in pure AA style, how may I be of help?

1 Like

What I’ve got is already a fair way beyond that tbh. Yes they’ve spent a bit more time making it look pretty, but realistically you’re going to end up with a ridiculous number of sheets, and it doesn’t deal with a lot of the issues I mentioned!

I’ve solved some of the issues - the use of Excel’s table function will solve some of my issues with variable data set sizes. Also some judicious extra features of Pivot Tables that I’ve not used before.

But something like OpenBom may just be the tool I should actually be using, if I can get my head round it enough.

1 Like

I can’t promise to be much help but ping me if you get stuck. There are quite a few tutorials on YouTube and the two guys in charge are pretty accommodating if you use the help.

I’ve pinged them a message asking if it’s right for what I want it to do, and asking for a recommended idiot’s guide to the terminology etc. I tried watching the intro video on the help section and it clearly assumed a lot of knowledge that I didn’t have, but that even a low level engineer was probably likely to know.

1 Like

Excel is usually a tool that can be made to do what you want, although there is usually a better way!

The key to getting it to work is to understand what shape of data is best, and then how to reference it. Do you know about the MATCH, INDEX and INDIRECT functions? Can you write scripts in VBA?

I can help if you like.

I’ve recently been made aware of MATCH, INDEX and INDIRECT, but haven’t had time to play with them yet to get a proper understanding. I’m very definitely trying to avoid getting into VBA - if I have to go that far, I might as well do it properly and create a proper database app.

VBA can be the best solution (in Excel) to variable data set sizes: there are 17 ingredients in this recipe, so fill that table to 17 rows.

A database is always better, but it can be a hassle to set up and maintain. Excel can work well, but can be easy to break. Fortunately we know you have no history of destroying software systems :+1:

Yeah, the Tables functionality looks like it might just cover what I need. It’s going to be a bugger to implement because the referencing is a bit hit and miss when you do copy / pasting, but after that I think it should handle all that rather better. Will also make a lot of my formulas elsewhere easier to read without spending a whole day adding in a ridiculous number of named ranges.

Yeah tables were introduced to bring some database functionality into Excel. As long as you use them properly they do work. Our spreadsheet audit team used to hate them; I think this was because they didn’t understand them well enough, and it’s no longer all in easy to understand Excel formulae.

Yeah, I can imagine in a large infrastructure place with compliance requirements and all that stuff they’d want to keep things as simple as possible. Thankfully, it’s just me and ocassionally the Wench using this one, and whilst the information is important to me, there’s no compliance headaches or similar bullshit to worry about.

Oh it wasn’t compliance, they audit huge project finance models created by bankers who think they’re really clever.