Results 1 to 6 of 6
Like Tree1Likes
  • 1 Post By excel-proficient

Using value from cell "above"

This is a discussion on Using value from cell "above" within the Excel Questions forums, part of the Question Forums category; How to you get Excel to copy into the active cell whatever happens to be in the cell above? If ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    20

    Default

    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)


  2. #2
    New Member
    Join Date
    May 2002
    Posts
    8

    Default

    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.

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Click on the appropriate cell to see the formula:

    Microsoft Excel - Book1___Running: xl2000 : OS = Windows NT5.0
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    =

    A
    B
    C
    D
    E
    F
    G
    H
    2








    3








    4








    5


    1text


    6








    7








    8




    0.5text
    9








    10








    Sheet1

    To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    The above image was automatically generated by [HtmlMaker 2.10] If you want this FREE SOFT, click here to download
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,837

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Jul 2009
    Location
    Edinburgh, UK
    Posts
    92

    Default 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
    trodneyrotter likes this.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com