A bit of help with excel

[Kilted Rogue]

Ok I know in this vast expanse that is XTMS there has to be more than one Tech savey kiltie who knows a bit about Excel.

I need a assistance with a lookup formula. I am building a production sheet for my kitchen and i have the main sheet complete,but i need to add a table/array/?? to it. This will tell the main sheet what the value of a word command will be. I know Billy your confusing us.

I have the sheet saved online Via Google Docs. and i can allow ppl to view and edit it. the easy way i feel to sort this out is to take a peek at it, read the following.

http://spreadsheets.google.com/ccc?k...WRVRma0E&hl=en



OK now your with me.
OK Sheet1 is the production sheet. when you put a number value in the PPL collum it will math out the row and divide the items accordingly

but i still have to tweek the number if the party wants special items.

Sheet 2 is a break down of the party types. that is where i will set up the table for the lookup formula. I have added a list of what the parties get when.

I want to be able to use the keywords in Sheet 2 collum B, to tell sheet 1 collum C how to divide the breakfast items per what type of party it is.

I hope that makes sence. PLEASE ask for more details from me. you have the question i will give the answers.

Billy

If you want to edit the sheet PM me i will allow permission

[Smayniac]

this is the sort of thing I have tried to figure out how to do in excel myself without success. Someone else at our company is currently taking up the torch. formulas and datamining other sheets I have down. making it think of words eludes me too. Good Luck
Bob

[Kilted Rogue]

I think it has to do with the Lookup ,Vlookup, or Hlookup formula

But i am lost after that.

[Rex_Tremende]

I do VLOOKUP functions all the time.

Looks like you do need to add an array which states the standard number of products of each thing to include for a given type of party - which is variable according to your business plan.

The formulas in your products columns needs to take the value in the type column, look it up in your array to find the corresponding quantity for the type and multiply it by the value in the PPL column, if I understand what it is that you are trying to accomplish.

Looks like at the bottom you would also be doing a calculation to determine the number of batches you need to make of a thing, but that's simple division.

The key to making a lookup function is to first name your array before you do anything, and it doesn't hurt to name your other variables, either - you can do this easily by replacing the cell address with a name of your choosing. This comes in very handy later when copying and pasting a series and you don't want the source cell inputs to drift.

The second key to this type of application is to use the FALSE (exact match) option and have good error handling. This usually requires embedding your VLOOKUP function inside an IFERROR function (or other nested IF statements on older versions).

Hope that helps.

Regards,
Rex.

[Kilted Rogue]

Thank you Rex, Now i just have to translate your post, so i can apply it. I like working in Excel, but i always have trouble with the formula wording/direction.