Re: I need some help please
The new thread was closed so I guess we will have to use this one. Here is the message I was going to post in reply to your comment Logit.
NPSS_quote_sheet contains an area to enter of services delivered to create quotes. The four things entered at the top, Case worker, Organisation, Locality and Child/YP relate to every row entered in the table below it. It used to be a table, but I want it to be a range now. I want to keep the same format as in the file I sent, for instance, the case worker needs to be in B6 and the data starts in A11. Columns I to N are hidden. Once all the items in a quote have been entered, my supervisor will review the quote to ensure that everything is correct and if he is satisfied with it, he will press Send to Costing tool. When the button, Send to Costing tool is pressed, every row starting at A11 and ending before the totals is copied to the costing_tool sheet.
For every row that is copied from NPSS_quote_sheet to the Costing_tool sheet, Caseworker, Organisation and Child/YP needs to be the same. Locality is used only for calculation of pricing purposes. Entered rows in costing_tool start in row 5 with the header in row 4, as in the sample file.
- For the first row, Date in A11 of NPSS_quote_sheet, needs to be copied to A5 in Costing_tool.
- For the first row, Service in A11 of NPSS_quote_sheet needs to be copied to E5 in Costing_tool.
- For the first row, Price ex. GST in H11 of NPSS_quote_sheet needs to be copied to H11 in Costing_tool.
- These 3 items change with every line in the quote.
Every row in the quote will have some cells that are the same for each line. The information that is entered at the top will be the same for every line in the quote.
- Case worker that is in the merged cells B6:D6 of NPSS_quote_sheet needs to be copied to G5 in Costing_tool but must also be copied to every row that is copied from NPSS_quote_sheet, so if there are 4 rows in NPSS_quote_sheet, when they are copied across to Costing_tool, the Date, Service and Price ex. GST might be different for each row, but every row will have the same Case worker. Therefore, in the example of 4 rows copied, the Case worker in B6:D6 of NPSS_quote_sheet will be the same for G5, G6, G7 and G8.
- Organisation in merged cells B7:D7 of NPSS_quote_sheet will be copied in the same fashion as Case worker and using the example of 4 rows as above, will be copied to F5, F6, F7 and F8 in Costing_tool.
- Child/YP in H7 of NPSS_quote_sheet, needs to be copied to using the same method as Case worker and using the example above, will be copied to D5, D6 D7 and D8.
When all the information is copied from NPSS_quote to Costing_tool, some additional information is filled in for every row. Each row has several formulas that need to be filled down to each additional row that is copied to Costing_tool. For the first row in Costing_tool, which is row 5, the following formulas need to be put in.
- A5: contains date
- E5: contains type of service
- I5: =IF(E5="*Activities",0,H5*0.1)
- J5: =IF(E5="Activities",H5,H5+I5)
- Z5: =TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "mmmm")
- AA5: =TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "yyyy")
- AD5: =CONCATENATE(AA5+1," - ",AA5+2)
- AG5: =IF(MONTH(A5)<7,YEAR(A5)-1,YEAR(A5))
- AH5: =IF(MONTH(A5)<7,YEAR(A5),YEAR(A5)+1)
- AI5: =CONCATENATE(AG5," - ",AH5)
- AJ5: =CONCATENATE(AI5," ","NPSS Work Allocation Sheet.xlsm")
- AK5: =CONCATENATE(AI5," ","Internal Work Allocation Sheet.xlsm")
Multiple quotes could be sent to one Costing_tool before they are moved on.
- When my supervisor is happy all the information has been entered, there are two dark blue buttons, “copy to relevant sheet and clear lines” and “copy to relevant sheet and keep lines”.
- Both of these buttons run a procedure that takes every row that has been added to the Costing_tool and copies them to the relevant allocation sheet.
- The relevant allocation sheet is determined by the date of each row.
- There are allocation sheets for every financial year.
- They are further broken down by the requesting organisation in column F for each row. If the requesting organisation is Ang Wes, the line gets copied to the financial year document that is reflected by the date.
- For instance, if the date occurs in the 2018-2019 financial year and has Ang Wes in the requesting organisation, the row will be copied to the “2018-2019 Internal Work Allocation sheet.xlsm”, but if there is anything else in the requesting organisation, the row will be copied to the spreadsheet with the title “2018-2019 NPSS Work Allocation Sheet.xlsm”.
- I hope that makes sense, if it doesn’t, reply and let me know and I will try and clarify it.
Each row will be copied to the appropriate allocation sheet and I somehow need to incorporate a feature that allows a date range to be entered. That is as some quotes or lines in the quote may apply to a span of several years. That means that I need to be able to enter a date range for the quote line and have the line broken up and pasted into the relevant allocation sheets.
- For instance, if a quote item is dated from the 20/9/18 to 20/9/20,
- the line will need to be broken up into the 2018-2019 allocation sheet, 2019-2020 Allocation sheet and the 2020-2021 allocation sheet.
- The first line, in the 2018-2019 allocation sheet will need to be dated from the 20/9/18 to 30/6/19.
- The second line will need to be dated from 1/7/19 to 30/6/20 and go into the 2019-2020 allocation sheet.
- The third line will be dated from 1/7/20 to 20/9/20 and go into the 2020-2021 Allocation sheet.
- The price ex. GST will need to be a proportion of the time in the year over the total time of the quote to be an accurate representation of the portion of the time spent in that year. For instance, if the price ex. GST for a quote line was 9 and started 1/1/19 and went until the 30/6/20, 6 months would apply to the 2018-2019 financial year and 12 months would apply to the 2019-2020 financial year. This would mean that in the 2018-2019 financial year allocation sheet, the price ex. GST would be 3 and in the 2019-2020 allocation sheet, the price ex. GST would be 6.
I have uploaded a allocation sheet if you need to look at but it is rather a simple, self explanatory document.
https://www.dropbox.com/s/se5g3miw1so4jq3/2018 - 2019 NPSS Work Allocation Sheet.xlsm?dl=0
Thanks so much!!!! I really appreciate this!!! I have been working on this for months and using the tables, it is not working. I have my supervisor asking me to show him some results and I have been starting to panic as I can’t code very well so I can’t do this myself.
I hope I haven’t scared anyone off by my long winded explanation and I have tried to explain this as best I can, but if any of it doesn’t make sense, message me back for clarification.
Thanks guys,
Dave
