Thread: [Solved] How can i add to a cell address? My existing formula isn't working.
View Single Post
 
Old 02-02-2019, 11:41 AM
xor xor is offline Windows 10 Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
Here is a collection, but really they are all very much poorer than Pecoflyer's formula.

=OFFSET(INDIRECT(B2),C2,,,)
=INDIRECT(LEFT(B2,3)&LOOKUP(9^9,--RIGHT(B2,ROW($1:$100)))+C2)
=OFFSET(INDIRECT(ADDRESS(CELL("row",B2)+C2,CELL("c ol",B2))),,-1)

Your own formula =INDIRECT($B2)+C$2 returns 20-May-2004 because INDIRECT($B2)
returns 38126 which is the serial number for the date 19-May-2004 and 38126 + C2 = 38126+1 = 38127 which is the serial number for the date 20-May-2004. Try it out by entering 38127 in a cell and format that cell "dd-mm-yyyy".

I didn't follow your initial intent because I thought it would be bad advice.

Please note that there shouldn't be a space between c and ol in the last OFFSET-formula.
Reply With Quote