Microsoft Office Forums

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
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
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
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,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

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,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

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,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
  #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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:16 AM.


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