Dynamic Range - Copying data

Resolved · Low Priority · Version 2016

Thu 13 Apr 2017, 11:44 Delegate Donna said...

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?

Tue 18 Apr 2017, 12:24 Trainer Jens said...

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

Thu 27 Apr 2017, 13:23 Trainer Jens said...

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

Thu 11 May 2017, 13:47 Delegate Donna said...

RE: Dynamic Range - Copying data

Thank you Jens! That's perfect.

Thu 27 Apr 2017, 13:37 Trainer Jens said...

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

Tue 25 Apr 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Moving between split pane sections in a spreadsheet

If you have used the split panes feature in your worksheet, use the following keyboard shortcut keys to move quickly and effortlessly between paned sections:

F6 - Move to the next pane
Shift + F6 - Move to the previous pane

View all Excel hints and tips