Copy paste a blocked cell in a column

**prarip** · 09-10-2023, 09:45 PM

I would like to create a column of formulas that looks like this:

1st cell

Please Login or Register  to view this content.

2nd cell

Please Login or Register  to view this content.

3rd cell

Please Login or Register  to view this content.

But if I copy paste the 1st cell in the column I get this result instead:

1st cell

Please Login or Register  to view this content.

2nd cell

Please Login or Register  to view this content.

3rd cell

Please Login or Register  to view this content.

How can I get the first result?

Thank you very much

**6StringJazzer** · 09-10-2023, 10:26 PM

For your situation there is no reason to use the "$" in the formula. Use

=A1-B1

in row 1 and copy down.

**prarip** · 09-10-2023, 10:54 PM

I omitted some informations.
My formula in reality is a subtraction between the value of a cell in another excel file and the value of a cell in my current excel file.
So, the cell looks like this:
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$2-B2
The reason I need A2 to not change it's because I order often the columns from A to Z or from higher cost to lower cost and I need that the formula always refers to the same cell on the other excel file.

**HansDouwe** · 09-10-2023, 11:07 PM

If you copy down your formula $A$2 does not change and B2 changes to B3, B4 and so on (conform your requirement)

Formula:

Please Login or Register  to view this content.

**6StringJazzer** · 09-11-2023, 09:21 AM

Originally Posted by prarip

I omitted some informations.
My formula in reality is a subtraction between the value of a cell in another excel file and the value of a cell in my current excel file.
So, the cell looks like this:

Mi dispiace but that is completely different than your original question. This formula does not have the same problem you described in your first post.

**prarip** · 09-11-2023, 01:03 PM

I'm sorry, I thought that writing it like in my first post was clearer but I created confusion.
So, I would like to achieve this result without having to write manually every cell:

='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$1-B1
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$2-B2
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$3-B3
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$4-B4
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$5-B5
etc.

If I copy paste the first cell I get this result instead:

='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$1-B1
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$1-B2
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$1-B3
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$1-B4
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A$1-B5

Thank you

**HansDouwe** · 09-11-2023, 02:47 PM

Please replace $A$1 by $A1.
In that case if you copy down, you will get $A1, $A2, $A3 and so on.

$A$1 is the same reference as $A1, $A$2 is the same reference as $A2 and so on.
The only difference when copying is that they change differently.

**6StringJazzer** · 09-11-2023, 04:31 PM

Change $A$1 to $A1 as HansDouwe says. If you sort the rows in AnotherExcelFile the formula in the first row will still refer to A1, second row A2, etc.

**prarip** · 09-11-2023, 10:47 PM

Thank you for the answers but unfortunately it seems like there's not an easy way to achieve what I need and I'm not explaining well.

So, in my excel file I have a list of 100 products ordered from A to Z.
In 'AnotherExcelFile' I have the same list of 100 products ordered from A to Z, the difference it is only that they can have different prices (price is in column B in my excel file and in column A in 'AnotherExcelFile').
So, the product in the first row in my excel file corresponds exactly to the same product in the first row in 'AnotherExcelFile'.
I don't want to compare the price of 2 different products, I want to compare the price of the same product.

The problem comes because in my excel file I need to change the order often for various parameters.
But if I change the order in my excel file my products price doesn't refer anymore to the corresponding same product price in 'AnotherExcelFile'.

So, if I copy paste "='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A1-B1" my columns will look like this:

='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A1-B1
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A2-B2
='C:\Users\Username\Desktop\[AnotherExcelFile.xlsx]Foglio1'!$A3-B3

But if I change the order in my excel file from Z to A for example, since the order in AnotherExcelFile is from A to Z I will get the difference in price between 2 products that are not the same product.
I want to compare the change in price for apples, not between apples and bananas.

**6StringJazzer** · 09-11-2023, 11:06 PM

Let me try to rephrase what you want to confirm I understand this:

You have a list of products in file A with a price for each product
You have a list of the same products in file B with a price for each product
The price for a given product can be different in the two files
In file A you want to do a comparison of the two prices
You want to be able to sort the product list in file B arbitrarily and still be able to reference the corresponding products from file A.

I am going to suggest that your approach of trying to find a formula in file A that uses a row number that will always refer to the correct product in file B when the row changes in column B is not going to work.

Another approach is to use the product to do a lookup of the price. Unfortunately the VLOOKUP command (XLOOKUP in newer versions of Excel) will not work on a closed workbook. I am going to suggest you create a mirror sheet in file A that references the data in file B. Then you can use VLOOKUP on the mirror sheet to retrieve the price.

1. Create a new sheet in file A. Name it Mirror. Put this formula in cell A1:

Formula:

Please Login or Register  to view this content.

Instead of B use the actual last column of data you want to reference, and obviously, use the real file name.
Copy this formula to the right for as many columns as needed, and down for the maximum number of products you expect to ever have.

Close File B if it is open. The data in sheet Mirror should still be there.

2. You have not described the layout of your data in either file, so I am going to have to make assumptions. You may have to change the column references here.

In file B assume that Product is in column A, price is in column B. This will also be true in file A, sheet Mirror.

In File A assume the sheet containing the product list is in Sheet1, and also the product is in column A and price is in column B.

In File A Sheet1, put the following formula in C1 to get the price of the product in the row from file B:

Formula:

Please Login or Register  to view this content.

Fill down for as many rows as needed.

If you have any problem implementing this, attach samples of File A and File B with any private data removed or disguised and I can do it for you.

**prarip** · 09-13-2023, 01:29 PM

Thank you so much for your help.
I've tried again your first formulas without the '$' and it is working properly.
In fact if I change the order in the first excel file the reference to the cells in the other excel doesn't change.
I feel ashamed to have wasted your time, next time I will do more checks before replying to your answers.
Have a good day!

**HansDouwe** · 09-13-2023, 02:25 PM

You are Welcome!

Thanks for the feedback and glad to have helped.

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Copy paste a blocked cell in a column

LinkBack

Thread Tools

Rate This Thread

Display

Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Re: Copy paste a blocked cell in a column

Thread Information

Users Browsing this Thread

Similar Threads

copy Formula & paste in same column next cell when previous column is not blank

Copy Paste Issue Merge Cell & Paste Values other Column

[SOLVED] VB Code to copy paste data from column to another column based on content of cell

Copy data and paste to another column incrementing that column to next empty cell

[SOLVED] Window gets blocked with copy paste

Vba - find next empty column, insert column, copy paste values & copy paste formulas

[SOLVED] Copy and Paste a specific Column to a Column that's heading matches a particular cell

Bookmarks

Bookmarks

Posting Permissions