Formula to copy another cell's contents - Help!
 

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the cell

Is this possible?

Many Thanks

Formula to copy another cell's contents - Help!
 

=INDIRECT("B1")

Mike
"Richorton" wrote:

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the cell

Is this possible?

Many Thanks

Formula to copy another cell's contents - Help!
 

Thanks for your speedy response!
I don't suppose there's an easy way to auto fill that formula for the 150
rows I have is there?

"Mike H" wrote:

=INDIRECT("B1")

Mike
"Richorton" wrote:

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the cell

Is this possible?

Many Thanks

Formula to copy another cell's contents - Help!
 

You could use:
=indirect("B1")
to always point at B1


Richorton wrote:

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the cell

Is this possible?

Many Thanks

--

Dave Peterson

Formula to copy another cell's contents - Help!
 

Try,

=INDIRECT("B"&(ROW(A1)))

Mike

"Richorton" wrote:

Thanks for your speedy response!
I don't suppose there's an easy way to auto fill that formula for the 150
rows I have is there?

"Mike H" wrote:

=INDIRECT("B1")

Mike
"Richorton" wrote:

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the cell

Is this possible?

Many Thanks

Formula to copy another cell's contents - Help!
 

try this

Sub fillindirect()
For i = 1 To 150
Cells(i, "L").Formula = _
"=indirect(""b" & i & """)"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Richorton" wrote in message
...
Thanks for your speedy response!
I don't suppose there's an easy way to auto fill that formula for the 150
rows I have is there?

"Mike H" wrote:

=INDIRECT("B1")

Mike
"Richorton" wrote:

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value
would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the
cell

Is this possible?

Many Thanks

Formula to copy another cell's contents - Help!
 

If you have 150 rows worth of data in column B, then just double click on
the fill handle [square black blob at the bottom right-nand corner of the
cell] for cell A1.
Other options:
Grab the fill handle for A1 and drag it down A2 to A150, or
Copy A1, select A2 to A150, Paste.
--
David Biddulph

"Richorton" wrote in message
...
Thanks for your speedy response!
I don't suppose there's an easy way to auto fill that formula for the 150
rows I have is there?

"Mike H" wrote:

=INDIRECT("B1")

Mike
"Richorton" wrote:

Is there a formula that will show what the contents of another cell is.

Example

A1 =(B1)
The Value of B1 is 'Test'
Therefore the value of A1 is 'Test'

However if I were to drag Cell B1 to, lets say, C1
The formula in A1 would automatically change to =(C1) and the value
would
remain 'Test'.

I want it so the formula in A1 stays as =(B1), no matter if I move the
cell

Is this possible?

Many Thanks