Using value from cell "above"

philipjsmith

New Member
Joined
May 19, 2002
Messages
20
How to you get Excel to copy into the active cell whatever happens to be in the cell above? If you know the cell above contains a numerical value how do you use this value in a formula in the active cell ( say for example you wanted to halve it ) & if you know the cell contains text how do you copy that text into the active cell?
And suppose that cell is not above but say 3 cells up & 2 cells to the left? Can it still be done?
(Without using VBA)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
to put (say) 0.5 times the value of ANY other cell in an active cell :
in the active cell type =0.5*
then click the cell that you want 0.5 OF.

to copy a value from one cell to another (even text):
in the cell where you want the answer type = and then select (pick) the cell you want to copy.
 
Upvote 0
On 2002-05-31 11:17, philipjsmith wrote:
How to you get Excel to copy into the active cell whatever happens to be in the cell above? If you know the cell above contains a numerical value how do you use this value in a formula in the active cell ( say for example you wanted to halve it ) & if you know the cell contains text how do you copy that text into the active cell?
And suppose that cell is not above but say 3 cells up & 2 cells to the left? Can it still be done?
(Without using VBA)

I'm very much reluctant to suggest these formulas:

=IF(ISNUMBER(OFFSET(C6,-1,0,1,1)),OFFSET(C6,-1,0,1,1)/2,OFFSET(C6,-1,0,1,1))

to be entered in C6 (the active cell).

The -1 can also be: -aCellRef (like -A1)

=IF(ISNUMBER(OFFSET(D6,-3,2,1,1)),OFFSET(D6,-3,2,1,1)/2,OFFSET(D6,-3,2,1,1))

to be entered in D6 (the active cell). Again: -3 and 2 can be replace by cell refs (like -A1 and A2).

Aladin
 
Upvote 0
Using R1C1 reference style...

=IF(ISTEXT(R[-1]C),R[-1]C,R[-1]C/2)

>And suppose that cell is not above but say 3 cells up & 2 cells to the left?

=IF(ISTEXT(R[-3]C[-2]),R[-3]C[-2],R[-3]C[-2]/2)
This message was edited by Mark W. on 2002-05-31 15:07
 
Upvote 0
Re: Using value from cell "above"

I know that you didn't want the VBA solution, but it may be worth for other users directed to this topic.

VBA code for value from cell abovr is:

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top