Microsoft Office Forums How can i add to a cell address? My existing formula isn't working.

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2019, 11:19 PM
Footahype Footahype is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Novice
How can i add to a cell address? My existing formula isn't working.
 
Join Date: Feb 2019
Posts: 4
Footahype is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 02-02-2019, 01:10 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How can i add to a cell address? My existing formula isn't working. Windows 7 64bit How can i add to a cell address? My existing formula isn't working. Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,396
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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.
Reply With Quote
  #3  
Old 02-02-2019, 01:42 AM
xor xor is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

If I understand you correctly, put in E2

=INDIRECT("r["&+C2&"]c1",0)
Reply With Quote
  #4  
Old 02-02-2019, 08:36 AM
Footahype Footahype is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Novice
How can i add to a cell address? My existing formula isn't working.
 
Join Date: Feb 2019
Posts: 4
Footahype is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 02-02-2019, 08:56 AM
xor xor is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #6  
Old 02-02-2019, 11:09 AM
Footahype Footahype is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Novice
How can i add to a cell address? My existing formula isn't working.
 
Join Date: Feb 2019
Posts: 4
Footahype is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 02-02-2019, 11:41 AM
xor xor is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
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
  #8  
Old 02-02-2019, 01:19 PM
Footahype Footahype is offline How can i add to a cell address? My existing formula isn't working. Windows 10 How can i add to a cell address? My existing formula isn't working. Office 2016
Novice
How can i add to a cell address? My existing formula isn't working.
 
Join Date: Feb 2019
Posts: 4
Footahype is on a distinguished road
Default

Thank you. Your explanation was perfect. Definitely a great mind
Reply With Quote
Reply

Tags
excel 2013 formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i add to a cell address? My existing formula isn't working. 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
How can i add to a cell address? My existing formula isn't working. 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


All times are GMT -7. The time now is 08:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft