What would be the best formula?

[dpaton05]

I have a spreadsheet that looks like this, https://www.screencast.com/t/EhxupL7A. The spreadsheet is for developing quotes. CSC stands for community service centre and is a drop down list next to it allowing you to chose the CSC. Each CSC will be in a different location, remote, regional or metropolitan. That then affects the price that workers are paid for a service in that area, for instance, remote areas might attract a higher rate then regional areas. The date is when it is performed and will also change the price of a service, for instance, workers will receive more on a weekend and more again on a public holiday. I have a list of public holidays and I have worked out how to have them all based on formulas so the dates of them calculate each time the spreadsheet is opened. The service is a drop down list with about 8 activities the worker could perform. These will all have different effects on the price. Here is a picture of the contents of that list https://www.screencast.com/t/vdVKdBOW4.

Then there is hrs taken and kms travelled fields. This is followed by a total field.

I am trying to adapt it to use instead of this one my manager is using and he wanted me to simplify it so there is less inputs. https://www.screencast.com/t/wSocY3UjzShi

Here is a picture of some of some of the items from the deliverables list of the original spreadsheet https://www.screencast.com/t/3HTfH0bo

This is a picture of some of the items from a Services price list table in another tab. https://www.screencast.com/t/9lFB3tYMBeB

This is the main formula that is used to calculate the price on the original spreadsheet =IF(OR(ISBLANK(A4),ISBLANK(C4),ISBLANK(D4)),0,VLOOKUP(CONCATENATE(A4," ",C4," ",D4),Service_Price_List,2,FALSE))


What I need to know if what would be a good formula I could use for this new spreadsheet. I have all the tables and everything from the original spreadsheet in the new spreadsheet, just not so many fields to enter information. He wants me to program it so it figures out most things by itself without needing to enter.

Could someone help me please with an appropriate formula that would be efficient in doing this. I don't know if I have added enough info on here about it for anyone to determine the best formula to use, if I need to add more, let me know. I originally was thinking I could use many nested Ifs as I am not that familiar with formulas in excel but that would be very messy.

Thank you,
Dave

 

[Dave Patton]

The only part that I reviewed was your paragraph regarding the Lookup formula.
N.B. We do not know your business challenges, data structure, and requirements.

The two different examples below show some ideas that you may find useful.

Excel 2010
	A	B	C	D	E	F
1
2	Version a
3	Info_A	Info_B	Info_C	Info_D	Price
4	A	other info	B	C	200.00
5
6
7	N.B.
8	There is also data in B4
9	The Service_Price_List is edited to use the delimiter "|" instead of " ".
10
11	Version b	There is a distinct Lookup Table for each CSC and Area
12	Info_A	CSC	Area	Info_D	Price
13		B	B	C	325.00
14
1e

Cell Formulas
Range	Formula
E4	=IF(COUNTA(A4:D4)=4,VLOOKUP(A4&"|"&C4&"|"&D4,Service_Price_List,2,0),0)
E13	=CHOOSE(MATCH("r"&B13&C13,{"rAB","rBB"}),VLOOKUP(D13,rAB,2,0),VLOOKUP(D13,rBB,2,0))

Named Ranges
Name	Refers To	Cells
rAB	='1d'!$H$14:$I$16
rBB	='1e'!$H$14:$I$16
Service_Price_List	='1e'!$H$4:$I$4

 

[dpaton05]

In you second formula, what do you mean by "r", "rAB", rAB, rBB and "rBB"?

 

[Dave Patton]

per Above "There is a distinct Lookup Table for each CSC and Area"


The first one is named rAB r for Range A for CSC and B for Area. etc

 

[dpaton05]

What does "|" do in the formula?

 

[Dave Patton]

In my suggestion, I stated
The Service_Price_List is edited to use the delimiter "|" instead of " ".

The delimiter just separates the data that is being concatenated. It may be easier to review the formula with a visible delimiter compared
to using a space " ".

 

[dpaton05]

I put in this formula but I still get 0, despite there being figures in the required cells. =IF(COUNTA(B13:F13)=4,VLOOKUP(D13&" "&C4&" "&D4,Service_Price_list,2,0),0). What have I done wrong?
The service price list is a long list of every combination of entries for just one of the programs. There are 2 other programs so the list is 3 times that long. Is there a formula that will allow me to access the all of this information more simply than this?

 

[dpaton05]

I tried to edit the last post but it wouldn't let me. Here is the extra bit I was going to write:

This is what some of the Service_Price_list looks like, it wouldn't fit all on one page. https://www.screencast.com/t/wEfyucZvFV6d

Is there a formula that will allow me to access the all of this information more simply than this? I have also turned all the data into 11 smaller tables. Here is a sample of the supervised contact tables as they wouldn't all fit on the screen. https://www.screencast.com/t/arV5uGDw

 

[Dave Patton]

from post 2

Excel 2010
	B	C	D	E	F	G	H	I
11	Version b	There is a distinct Lookup Table for each CSC and Area
12	CSC	Area	Service	Price			Prices CSC B Area NW
13	B	NW	Work - a	12.00			Service	Price
14							Work -123	325.00
15							Work - a	12.00
16							Work -z	1,000.00
1e

Cell Formulas
Range	Formula
E13	=CHOOSE(MATCH("r"&B13&C13,{"rAB","rBNW"}),VLOOKUP(D13,rAB,2,0),VLOOKUP(D13,rBNW,2,0))

Named Ranges
Name	Refers To	Cells
rAB	='1d'!$H$14:$I$16
rBNW	='1e'!$H$14:$I$16

Albert Einstein

Everything should be made as simple as possible but not simpler.

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H	I
11	Version b	There is a distinct Lookup Table for each CSC and Area
12	CSC	Area	Service	Price			Prices CSC B Area NW
13	A	B	C	375.00			Service	Price
14	B	NW	Work-z	1,000.00			Work -123	325.00
15
16	or
17	B	NW	Work-z	1,000.00			Work - a	12.00
18	A	B	C	375.00			Work-z	1,000.00
1e

Cell Formulas
Range	Formula
E13	=CHOOSE(MATCH("r"&B13&C13,{"rAB","rBNW"}),VLOOKUP(D13,rAB,2,0),VLOOKUP(D13,rBNW,2,0))
E14	=CHOOSE(MATCH("r"&B14&C14,rTbls),VLOOKUP(D14,rAB,2,0),VLOOKUP(D14,rBNW,2,0))
E17	=VLOOKUP(D17,INDIRECT("r"&B17&C17),2,0)
E18	=CHOOSE(MATCH("r"&B18&C18,{"rAB","rBNW"}),VLOOKUP(D18,rAB,2,0),VLOOKUP(D18,rBNW,2,0))

Named Ranges
Name	Refers To	Cells
rAB	='1d'!$H$14:$I$16
rBNW	='1e'!$H$14:$I$18
rT	=zList!$B$2:$D$11
rTbls	='1e'!$M$2:$M$12

The named range "rTbls" has the names of Lookup Tables for the respective CSCs and Areas