Donna has attended:
Excel Pivot Tables course
Dynamic Range - Copying data
Will try to explain this clearly!
I have a workbook that has separate sheets for each month. Each row details a different product, how much it costs (pay), how much it's sold for(charge), the profit, no. of products sold and the monthly GP.
The profit is a formula of =cost(charge)-sold(pay) price, and the monthly GP is formula =profit x no. of products.
For new products this line is added to current month and then added to the following worksheets.
However, the cost or sold price can change through the months. When changing the cost or sold price the profit is not changing as it is pulling the formula from the original dynamic range,
i.e. =tblApr17[@[charge rate ]]-tblApr17[@[Pay rate ]]
Is there a way of pasting that doesn't include the table reference to avoid this?
RE: Dynamic Range - Copying data
Hi Donna,
Thank you for the forum question.
You have many options to tell Excel how you want to paste. If you on the Home tab in the Clipboard group click on the down arrow below paste, you will get a dialog box with the options.
You can paste only values, values with formatting and much more.
I hope that you can find a way. If not I can have a look at your sheet.
If you want me to look at the workbook, you can send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Dynamic Range - Copying data
Hi Donna,
I have got your file and I am very sorry that you had to wait for an answer, but I have been out of office for some days.
When I copy and paste from your April sheet and paste the records in the May sheet I do not get it wrong as you do. I have no good explanation why. May I suggest that next time you copy records from one dynamic range to another, please go to the formulas tab and click Show Formula in the Formula Auditing group. This will show all formulas the the worksheet. If it happens again that your paste the links from the source dynamic range, copy down the right formula.
I have never experience this issue before and I have not been able to google the issue.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Dynamic Range - Copying data
Hi Donna,
I just learned something new about Excel. If you type the cell references (C4-B4) instead of click on the cells and get the code (=[@profit]*8) you will not get the problem, when you copy and paste to another dynamic range.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
