function which takes address() and gives value

excel excel-2007
a-z picture a-z · Jul 9, 2012 · Viewed 56.8k times · Source

I want to make a cell equal to the cell above it.

When I write

=address(row()-1;column())

It just writes the name of the cell above(for example for B2 it writes $B$1)

Is there a function which inputs the address and puts the value?

Answer

danielpiestrak picture danielpiestrak · Jul 9, 2012

Try using the OFFSET function to offset the cell by -1 row. For example you can paste the below formula into cell A2 and it will return the value of A1:

=OFFSET(A2,-1,0)

If, however, you really want to keep your present address method, put it inside of the INDIRECT function like this:

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

Related questions

Append same text to every cell in a column in Excel

How can I append text to every cell in a column in Excel? I need to add a comma (",") to the end. Example: [email protected] turns into [email protected], Data Sample: [email protected] satishmm_2sptc@…

Read More
How to do a "Save As" in vba code, saving my current Excel workbook with datestamp?

I have an Excel Workbook that on form button click I want to save a copy of the workbook with the filename being the current date. I keep trying the the following ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(…

Read More
Closing Excel Application using VBA

I have used the following without success. The active workbook closes, indeed, but the excel window remains open. Application.ActiveWindow.Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=False Which is the command that terminates the application? EDIT To say a little …

Read More