Copy Paste Row with No changes - Help

[EllyD]

I'm newish to Excel and don't use it for anything hugely complex.

I am using Excel 2007.

I have 1 sheet with raw data with the financials of multiple companies.

I have a second sheet where the formulas are executed that refer to that raw data sheet.

My problem is copying and pasting rows on that second sheet.

When I copy the row with all my formula and then paste it somewhere underneath it doesn't copy it exactly, it changes the row numbers. I used Paste Special > All but it keeps changing the row numbers.

Example of one the cells that are in the copied and pasted row:

='Healthcare RAW'!C8/'Healthcare RAW'!C11​

But when I paste the row somewhere below that cell now reads

='Healthcare RAW'!C19/'Healthcare RAW'!C22​

Why is it changing the row number?

However, when I copy the formula of each individual cell and paste it below it duplicates it exactly. This is really labour intensive though.

I want to paste the row and then manually change only the letter C to whatever the column is in my raw data sheet.

I hope I've explained that clearly enough. Not sure if screenshots are better?

Any help would be greatly appreciated as I'm going boss-eyed here copying these tiny cells one at a time.

Thanks
Elly

 

[mcunningham]

Good afternoon Elly!

If I understand you question correctly, adding a dollar sign ($) in front of the part of the formula you do not want to change will work.

Example:

'Healthcare RAW'!C$8/'Healthcare RAW'!C$11

Hope this helps!
Megan

 

[Joe4]

'Healthcare RAW'!C$8/'Healthcare RAW'!C$11

Unfortunately, that won't work.
If you have a formula like that, and copy or autofill it down a column, Excel will still increase the numbers (try it and see!).

Elly,
Can you provide us with some details? There are some "tricks" you can do, but we would need to know the following:
1. What cell is this original formula in?
2. What cells are you copying it to?
3. How should the formula change (maybe show us what the first few that are copied should look like in their new cells)?

 

[EllyD]

Hi

Thanks very much for the replies. Here is a screenshot which I hope helps clarifies what I'm trying to do.

I've made the image as large as the forum system will allow me to.

Thanks
Elly

 

[Joe4]

But do you really want the formula to be EXACTLY the same, or do you want other changes?

There are a few things you can do.
You can pull up the original formula in edit mode (hit F2), then select it and press CTRL+C to copy.
Then escape out, go to the cell you wish to paste it to, hit F2 again, and then do a CTRL+V to paste it.

Another option is to set named ranges and use the name ranges in your formula.
Then when you copy and paste like you normally do, it copies the named range without any adjustments.

 

[EllyD]

Yes I do want it the same.

After pasting all I want to do is change the column number (same row number)that is referenced in the 'Healthcare RAW' sheet.

I don't know the logic in Excel changing the number when you paste your clipboard.

Thanks for your help anyway. I appreciate your time.

 

[Joe4]

After pasting all I want to do is change the column number (same row number)that is referenced in the 'Healthcare RAW' sheet.

If you follow the first recommendation I posted, it will copy the formula, exactly as-is, with no changes.

If there is some sort of logic (i.e. if, when copying the formula 10 rows down, you want the column reference to change by 10, like a transpose), we may be able to come up with a formula using OFFSET or INDEX that would do what you want without having to edit it all. But that would require it be be consistent, predictive logic (follows a hard-and-fast rule, and not just random columns that cannot be predicted). We would just need you to explain what that is.