

LinkBack  Thread Tools  Display Modes 
#1




How can i add to a cell address? My existing formula isn't working.
i "need" help with an excel project i'm working on.
i would like to add the number 1,2,3 or whatever amount to an existing cell address. For example cell address $A$2, i want to add 1 to it and display the contents of cell A3. For example: Column A Column B Column C Column D Date Address Add Results 19/05/2004 $A$2 1 20/05/2004 22/05/1970 2 26/05/2016 29/05/2003 02/06/1998 Formulas i have Cell B2 =ADDRESS(MATCH(A2,A:A,0),COLUMN(A1)) Cell D2 =IF(ISERROR(INDIRECT($B2)+C$2),"",INDIRECT($B2)+C$2) However in Cell D2 i get the "incorrect" result 20/05/2004. I want to get 22/05/1970. Please Help to correct my formula 
#2




Hi and welcome
why not in one go, something like =INDEX(A:A,MATCH(A2,A:A)+C2,0) Eventually post a sample sheet with some data and expected results
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank. 
#3




If I understand you correctly, put in E2
=INDIRECT("r["&+C2&"]c1",0) 
#4




Thank you Pecoflyer & Xor. Both solutions WORKED! you guys are awesome
Two questions tho: 1. The solution from Xor worked but seems abstract to me, because it doesn't include the use of cell B2. Kindly explain your formula? 2. In my example cell B2 contains the cell address $A$2, which i arrived by using the formula (=ADDRESS(MATCH(A2,A:A,0),COLUMN(A1)). i wanted to use the cell address $A$2 in B2 to find the answer. Can this be achieved? Thanks again. Awaiting your responses. 
#5




I think you should stick to Pecoflyer's solution. INDEX and MATCH are, contrary to INDIRECT, nonvolatile and thus much better (and also easier to understand).
There are lots of ways to solve this, among many others =OFFSET(A$2,C2;; Your formula =ADDRESS(MATCH(A2,A:A,0),COLUMN(A1)) could also have been =CELL("address",A2) Pardon me, but I think your column B approach is an unnecessary complication. By the way, shouldn't Pecoflyer's formula read: =INDEX(A:A,MATCH(A2,A:A,0)+C2)? Edit: In the second line in the OFFSET formula there should be 3 commas and an ending parenthesis. 
#6




Xor, point taken. i understand.
But, just for closure, knowledge and the benefit of others like myself that may have searched for this thread, what would be the formula if i want to add to the cell address $A$2 and then display the results. bear in mind this was the initial intent of the thread. 
#7




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 20May2004 because INDIRECT($B2) returns 38126 which is the serial number for the date 19May2004 and 38126 + C2 = 38126+1 = 38127 which is the serial number for the date 20May2004. Try it out by entering 38127 in a cell and format that cell "ddmmyyyy". 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 OFFSETformula. 
#8




Thank you. Your explanation was perfect. Definitely a great mind

Tags 
excel 2013 formula 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Entering a column value in a cell, to be used as part of a reference in a formula in another cell  paulkaye  Excel  4  02262017 04:18 AM 
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula  innkeeper9  Excel  2  09132016 08:59 PM 
Word 2016, autoformating existing text not always working  tammons  Word  7  08232016 12:47 AM 
Change formula cell range based on cell value  Scoth  Excel  4  10252012 07:51 AM 
Changing Cell address within a formula  vermont_past  Excel  12  09212012 12:15 PM 