Which Software/Help

[GRitchie]

I've got a food tech teacher wanting to create a recipes/prices document (with LOTS of links/calculations).

 

I can't decide whether or not I should be using Access or Excel to create this?

What do you think?

 

It will have a list of prices (including supplier, item name, cost per unit etc.)

and then a recipe (including supplier name, item name, cost per unit, students in class, cost per student, cost for class)

 

It's turning into quite a challenge already and don't want to get in too deep in the wrong program and have to back track.

 

Thanks!

[Cazale]

Sounds like a job for a relational database to me!

[howartp]

Hi.

 

From what you've said, I would probably be using Excel.

 

Sheet1 with the price lists, then Sheet2 onwards with one sheet per recipe.

 

Have a box at the top of each recipe that asks for the number of students in the class, so when she fills it in it recalculates the ingredients below, then she (presumably) prints it off for the technician to order?

 

Peter

[howartp]

Sample attached.

 

Used https://support.office.com/en-us/article/Create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b to remind myself how to do dropdown boxes.

 

Peter

Recipes.xlsx

[Cazale]

From what you've said, I would probably be using Excel

 

I would fundamentally disagree. Excel does an okay job as a data repository, but when you have two (or more) sets of relational data, you should be using a database.

 

Just like the OP stated, this is one of those situations where Excel would be perfectly fine for a small set of data but would soon be wishing he'd used a db when it starts to grow. Once you start adding to it, and adding to it again, it is going to grow unwieldy in spreadsheet format. Spreadsheets with multiple sets of data just don't scale well. Databases do.

Edited May 16, 2016 by Cazale

[GRitchie]

I've started something here...

I started it initially in excel, but then because she wanted a lot more links I thought oh maybe Access!

But can you use IF/VLOOKUPS etc. in access?

[spadam]

Depends not only on the data but also on who will be expected to use and maintain it, what their skills / knowledge are, how much training/support will be available... Hard to give a definitive answer based on the information available. May also want to consider a simple webapp.

 

I would say not to worry too much about having to backtrack.. If it turns out the original approach was unsuitable you'll have learnt a lot from the experience and v2 will be all the better for it

[howartp]

Maybe we need to know more about the requirements to make an informed suggestion. :-)

 

My first reply was "from what you've said" and included some assumptions. One of those being a food tech teacher isn't likely to have ever used Access, and another being she probably has a finite number of recipes that she wants to use in this situation.

 

For an ongoing, multi-dimensional, multi user scenario it's quite possible to use Access.

 

But for a single user, limited size dataset that isn't really multi-dimensional, Excel is equally capable.

 

In my opinion. :-)

[GRitchie]

Okay, well

The school are now going to offer purchasing food ingredients for students. The parents then just have to pay that fee each year (works out cheaper/bought in bulk.)

The food teacher is having this spreadatabase so that it can calculate all her costs for each recipe basically.

 

So yes, in theory there will be a limited number of recipes (or at least for this year it won't change).

 

If I was going to do a spreadsheet, I also thought having hundreds of recipe tabs would be a bit insane, so I thought maybe put them on sweet/savoury pages for KS3 and KS4.

Then, on the homepage, have a dropdown/search for the recipes and have it just jump to that cell?

 

We're meeting again at 11:15 so if anyone's active then for input that would be great!

 

Thanks :-)

[howartp]

Depends if you're in Cumbria, Lancashire or Leeds? :-)

 

(Your profile has all three)

[GRitchie]

Leeds is the football team, Cumbria's where I'm at

[GRitchie]

Sample attached.

 

Used https://support.office.com/en-us/article/Create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b to remind myself how to do dropdown boxes.

 

Peter

 

When I try to adjust the items in this list I get a #N/A or the wrong value?

I've updated your =FOODS group and changed the VLOOKUP table, but it just errors?

 

Bug with Office 2013 or incorrect forumla?

 

 

EDIT:

FIXED. Just had to add into the VLOOKUP to find an EXACT match :-)

Edited May 17, 2016 by GRitchie