In Excel How to use cell ref as a variable

Discussion in 'Software Q&A' started by abhijitroy, Jul 9, 2006.

Thread Status:
Not open for further replies.
  1. abhijitroy

    abhijitroy New Member

    Joined:
    May 18, 2005
    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    0
    In MS-Excel Suppose in Sheet1 A1,A3, A5,.... contains data. The cells A2,A4,.... are blank. These data are again used in ColC of Sheet2 by means of a formula. However, in Sheet2 no row is to be left blank. Thus, cell C1 of Sheet2 refers to cell A1 of Sheet1, cell C2 of Sheet2 refers to cell A3 of Sheet1, cell C3 of Sheet2 refers to cell A5 of Sheet1, and so on.
    So, how to copy the formula of C1Sheet2! to its succesive rows i.e., to C2Sheet2!, C3Sheet2!,... so that the row reference of sheet1 jumps by one row every time.
     
  2. Ramakrishnan

    Ramakrishnan The Researcher

    Joined:
    Mar 7, 2006
    Messages:
    1,154
    Likes Received:
    10
    Trophy Points:
    38
    Location:
    Mumbai
    Your problem can be easily solved. Copy the formula the normal way from Sheet1 to sheet2. You will get blank rows in between the formulas in sheet2. For deleting the blank rows do the following: Select the column with formula in sheet2. press f5. press specail. select blanks.press ok. now only the blank rows are selected. Edit>Delete>entire row will delete all these rows. You will get formulas as in sheet1 but without any breaks. I hope this will solve your problem
     
Thread Status:
Not open for further replies.

Share This Page