Formula Lookup

[KirstAnn1]

Hi,

I am creating a template for invoice creation. There are 300 pay items (pre quoted, and any number or combination can be used) and I have allowed a section for variations that happen ad-hoc.
All 300 pay items indivudially numbered, they all follow the same basic principal there is: (only exception is variations that all have item numbers ranging between 5000-5999 and they can have the same item number repeated based on the variation type, so I added area where they can be included then anything with that range of item number then has to be referenced into that section via text matching provided in a drop down list, kinda messy but I've tried my best) but neverthe less they follow the same convention of:
-UOM 1(unit of measure)
-rate 1
-qty used 1
-UOM 2
-rate 2
-qty used 2

To determine cost some pay items are simply (rate 1 X qty used 1) + (rate 2 X qty used 2) but others are (((qty 2 X rate 2) + rate 1) X qty 1) and others simply only have rate 1, UOM 1 and quantitiy 1

My problem is how to tell the "total" cell what "formula" to use based off the item number

Not sure if importiant but all cells here come from an input sheet in the same workbook so I was able to allow for the variations to come through

I have tried to go down the VLOOKUP pathway to then have the correct "formula" located next to the master pay item list and tried to use the OFFSET to then base off the refernce cell but with the variations, it was getting too lost in the formula so then tried to place this in the input sheet but again I was getting errors.

Also I am writing everything in formula, as no one else in the office can use VBA, so makes sense that if anyone else ever needs to change then they can follow a touch easier.

Thanks

 

[Toadstool]

Hi KirstAnn1,

It's difficult to follow your question but I think you're saying you have three different formats of items and you've used VLOOKUP to construct most columns of your invoice template but are unsure how to perform the different types of Total calculations?

If you are driven by data from another sheet then I assume that sheet has some kind of indication as to which of the three types an item is. In this example I'm using the first 8 rows as the Item data (to keep it simple) and the CHOOSE statement to retrieve the calculation type for each of 3 types. Maybe this will give you an idea on how to move forward.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Item	Calc
2		5555	1
3		222	2
4		777	3
5
6
7
8
9
10	Date	Item	Descriiption				Est Qty 1	Unit 1	Actual Qty 1	Rate 1	Est Qty 2	Units 2	Actual Qty 2	Rate 2	Total
11	7/7/2020	5555	Widget				10	tonnes	10	$200.00	2	5	4	$100.00	$2,400.00
12	5/12/2020	222	Stuff				1	shift	5	$4,600.00	1	n-shift	3	$5,060.00	$98,900.00
13	4/4/2020	777	Brinch Gasket				22	12	3	$250.00					$9,000.00
14
Sheet1

Cell Formulas
Range	Formula
O11:O14	O11	=IFERROR(CHOOSE(VLOOKUP(B11,$B$2:$C$8,2,0),(((I11*J11)+(M11*N11))),(((M11*N11)+J11)*I11),((J11*I11*H11))),"")

 

[KirstAnn1]

Yes you got your assumptions correct in what I was trying to look up.

Apologies for my bad explination.

Your formula looks great and from reading should work as needed. I will spend some time and get it into the spreadsheet but thankyou so much! It really helps. I hadnt though of placing the formula in that way to solve it, I knew I needed to nest it in someway just didnt know what and how. So thank-you again

 

[Toadstool]

Phew! Glad I got close.
You're welcome and thanks for the feedback.