I need to define a cell must contain the value of another cell .

[jackson7249]

In my workbook, Cell A1 contains a formula. I need to define cell B5 so that it contains the value in cell A1 and not the formula.

[AliGW]

Just this in B5:

=A1

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[jackson7249]

I am using Microsoft Office 365 running under Windows 11

[AliGW]

Then please update your forum profile, which tells us you are using Excel 2007!

Is what I suggested working for you?

If not, there are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[jackson7249]

Thanks ALIGW.

Cell A1 contains the result of a formula, the answer to which is 987. If I were to copy cell A1 to cell B5 and paste values, I will get the value 987 in cell B5 not the result of the formula. So, can I define cell B5 in such a way that it contains the contents of cell A1, but as value 987, not the formula.

[AliGW]

Yes, as I said above!

In cell B5:

=A1

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F
1	987	A1: =SUM(E1:F1)			900	87
2
3
4
5		987	B5: =A1

Sheet: Sheet1

Provide a sample workbook if this is not what you want.

[jackson7249]

Thanks again for your help.

Unfortunately that doesn't help as I am copying cell B5 within a macro to a cell in another workbook, so I need the value 987 in the other workbook cell.

Is there no cell format that I can use for cell B5, so that when I define cell b5 as =A1, the contents of cell b5 will be 987.

[AliGW]

No, there isn't. But you should be able to have the macro tweaked to take the value of the cell.

I'll move this to the VBA section - I can't help you with that, I am afraid.

[jackson7249]

Thanks again for your help.

Unfortunately that doesn't help as I am copying cell B5 within a macro to a cell in another workbook, so I need the value 987 in the other workbook cell.

Is there a cell format that I can use for cell B5 so that when I give it the formula =A1 and copy it to the other workbook cell, the contents of the receiving cell will be 987 and not A1 in the source workbook.

[jackson7249]

Thank you so much for trying to help.

It is much appreciated.