• CONTEST ALERT - Experience the power of DDR5 memory with Kingston Click for details

In Excel How to use cell ref as a variable

Not open for further replies.


Broken In
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.


The Researcher
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
Not open for further replies.
Top Bottom