#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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#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, non-volatile 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 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. |
#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 | 02-26-2017 04:18 AM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
Word 2016, autoformating existing text not always working | tammons | Word | 7 | 08-23-2016 12:47 AM |
Change formula cell range based on cell value | Scoth | Excel | 4 | 10-25-2012 07:51 AM |
Changing Cell address within a formula | vermont_past | Excel | 12 | 09-21-2012 12:15 PM |