Changing linking so instead of being between tables, it is linking between ranges

[dpaton05]

I have been working on a spreadsheet for months. This spreadsheet involved filling out information in a table, upon finishing, it would be copied to another table on another sheet, where further information would be added. Once this had been done, it would be copied to another workbook. The way I had it set up was a table on the first sheet which would be copied to a table on the second sheet and finally a range in the different work book.

I have been having all kinds of problems and I have had it recommended that I convert it all so it is going from range to range to range. I am not the best at coding, as I am still learning and I wanted some assistance. I am fine with formatting the sheets, just need help with the coding.

The last workbooks are financial year documents and each row in the first sheet will have a date which is transferred to the second sheet with the other relevant information for the row. The rows contain quotes for various services. Each row is transferred one row at a time.

Could someone please help me with the code I would use to transfer each row from one range object to the next?

I have attached a copy of my spreadsheet to give you some idea of what I want.

https://www.dropbox.com/s/fjljdrd0afd0wgs/quoting tool 11.7 WCI.xlsm?dl=0

Thanks,
Dave

 

[Logit]

Re: I need some help please

.
If I correctly understand your request, these are the fields to be copied from NPSS QUOTE sheet to COSTING TOOL sheet :

	A​	B​	C​	D​	E​	F​
1​
2​	Date		Service Price		ex. GST
3​	dd/mm/yyyy				10%Increase
4​

Is this correct ?

 

[Michael M]

Re: I need some help please

Dave
With respect, I did suggest that you were too far down the path on this workbook to change, but I personally wouldn't have used tables on this project.
That's simply my opinion.
I prefer to use tables on large datasets and only 1 or 2 rather than a lot of small tables on smaller datasets....again, that's simply my opinon.

 

[dpaton05]

Re: I need some help please

Michael, I know you suggested that I was to far down the path to change and that was your opinion, but I still think that changing would be a good idea for multiple reasons. I really respect your opinion as you are a lot better at coding then me. I only used tables in the first place as I thought it would be easier to get the formulas at the end of the table to auto fill for each record and I didn't know how to code so I didn't know how to insert the formulas with code.

Actually, that is the only reason I can think of. My choice to use tables was before I found this forum and I didn't realise that it was possible to get so much help with coding.

Logit, I will reply to your questions as soon as I can get to my pc but I am writing this on my phone.

Thanks,
Dave

 

[dpaton05]

Re: I need some help please

I just thought of something else, should I start a new thread with a more meaningful name and link this thread to it?

 

[Logit]

Re: I need some help please

.
Can you still edit the title for this thread ?

 

[dpaton05]

Re: I need some help please

New thread is linked here. Michael and logit, you should comment on the thread so you get subscribed to it.

https://www.mrexcel.com/forum/excel...ng-between-tables-linking-between-ranges.html

 

[MARK858]

Re: I need some help please

Dave, you don't need to comment on a thread to subscribe to it, you can just use the thread tools at the top of the first post in the thread

 

[dpaton05]

Re: I need some help please

Thanks Mark, I wasn't aware of that.

 

[dpaton05]

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