dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,362
- Office Version
- 365
- 2016
- Platform
- Windows
I have a table https://www.screencast.com/t/JtuMEa1Xp32, which has information that helps in generating quotes. I have multiple drop down lists allowing me to select the following information:
- The CSC is known, Customer service centre, and this allows me to determine if the location is remote, regional or metro. Stored on another tab is a table that has for every CSC in the list, whether it is remote, etc. The drop down, where the CSC is selected is situated at the top of the spreadsheet. The formula to the right side of the table, where it says Metropolitan is: =(INDEX(CSCs!G17:G99,MATCH(C4,CSC,0)))
The formula behid the Mon-fri cell near it is:
=IF(COUNTIF(Sheet2!G38:G48,B13),"Public Holiday",IF(WEEKDAY(B13,2)>5,"Weekend","Mon-Fri"))
This formula extracts from the date entered, if it is a public holiday, weekday or weekend.
The other columns for the table are as follows:
- Date
- What service or program, eg. Supervised contact, Supervised transport or Youth work support. (Drop down list)
- Hours taken
- Kilometres travelled
- Workers required.
- Price- this is the total.
I have worked out how to determine if the date is a public holiday, weekday or weekend automatically. I am just struggling a bit knowing which formula to use to extract the information from the table with the raw data, that needs to be multiplied by the hours and no of workers. Here is a picture of the table that I am trying to get the data from: https://www.screencast.com/t/QvJROWXzv9U I was going to use index and match but I didn't know how to make it refer to the data from sub headings. If anyone can think of a better way, can you let me know please or can you tell me how I am to do this with index and match?
- The CSC is known, Customer service centre, and this allows me to determine if the location is remote, regional or metro. Stored on another tab is a table that has for every CSC in the list, whether it is remote, etc. The drop down, where the CSC is selected is situated at the top of the spreadsheet. The formula to the right side of the table, where it says Metropolitan is: =(INDEX(CSCs!G17:G99,MATCH(C4,CSC,0)))
The formula behid the Mon-fri cell near it is:
=IF(COUNTIF(Sheet2!G38:G48,B13),"Public Holiday",IF(WEEKDAY(B13,2)>5,"Weekend","Mon-Fri"))
This formula extracts from the date entered, if it is a public holiday, weekday or weekend.
The other columns for the table are as follows:
- Date
- What service or program, eg. Supervised contact, Supervised transport or Youth work support. (Drop down list)
- Hours taken
- Kilometres travelled
- Workers required.
- Price- this is the total.
I have worked out how to determine if the date is a public holiday, weekday or weekend automatically. I am just struggling a bit knowing which formula to use to extract the information from the table with the raw data, that needs to be multiplied by the hours and no of workers. Here is a picture of the table that I am trying to get the data from: https://www.screencast.com/t/QvJROWXzv9U I was going to use index and match but I didn't know how to make it refer to the data from sub headings. If anyone can think of a better way, can you let me know please or can you tell me how I am to do this with index and match?