Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-24-2019, 07:13 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default Use of named ranges based on cell content

I have defined a few date named ranges (sub_1, sub_2,...) to be used in a formula to recalculate dates.


How can I use a particular cell value, e.g. value of cell A1=1,in the formula based and construct the formula with correct sub?
Formula, that does not do the job, would be something like '="sub_"&A1 +5' meaning the date calculated should be the date of sub_1 + 5 days
thanx
Reply With Quote
  #2  
Old 02-24-2019, 07:33 AM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 516
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

I think you would be better off adopting something like...

=IF(OR(A1={1,2}),IF(A1=1,sub_1,sub_2)+5,"")

Now in cell A1, you select between 1 and 2 to use either sub_1 or sub_2 plus the 5 days
Reply With Quote
  #3  
Old 02-24-2019, 08:55 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

Agree, but I have a dynamic number of subs and iím Not intended to update the formula for each sub
Reply With Quote
  #4  
Old 02-24-2019, 11:19 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

If you have A1:A5 named Sub_1 then you can enter in B1: =Sub_1+5 and copy down to B5.
Reply With Quote
  #5  
Old 02-24-2019, 11:27 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

Agree, but I would use the last digit (1, 2, ..) for the sub copied from another cell, as such Ďsub_í will be constant but extended with this number to Ďsub_1í and to be used in a formula
Reply With Quote
  #6  
Old 02-24-2019, 11:46 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

="Sub_"&A1+5
Reply With Quote
  #7  
Old 02-24-2019, 12:36 PM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

Doesnít work in my sheet,
Reply With Quote
  #8  
Old 02-24-2019, 01:00 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

You are not very helpful in just saying Doesn't work!

It works fine in my sheet.

You have chosen your name with care, haven't you?
Reply With Quote
  #9  
Old 02-24-2019, 01:26 PM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

Sorry youíre right but Iím currently not at home, Iíll reply tomorrow what exactly is shown in the file,
Reply With Quote
  #10  
Old 02-24-2019, 07:26 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

My apologies- I was too quick here. It doesn't work for me either. I guess we are back at something like what jefreybrown proposed.

Take a look at the attached. I am not sure if it is of any help.
Attached Files
File Type: xlsx Sub_x.xlsx (10.6 KB, 1 views)
Reply With Quote
  #11  
Old 02-25-2019, 12:02 AM
alpha alpha is offline Windows 10 Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 18
alpha is on a distinguished road
Default

Quote:
Originally Posted by Intruder View Post
Formula, that does not do the job, would be something like ="sub_"&A1 +5 ...
Instead of that, you can use:
Code:
=indirect("sub_"&A1)+5
Reply With Quote
  #12  
Old 02-25-2019, 12:13 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 409
ArviLaanemets will become famous soon enough
Default

Code:
=CHOOSE(MATCH($A$1,{1,2,3,4,5},0),sub_1,sub_2,sub_3,sub_4,sub_5)
Without an example what kind of data you have, and where and why those sub's are used (an example workbook uploaded here), it is difficult to give any better advice.

I myself prefer to use dynamic names. I.e. I select a cell in table, and then define a dynamic value, which depends on table row and values in this row (or in some other cell in workbook). All cell references bound to active row are given with relative row number. The value returned by name defined in such way will be different for different table rows.
Reply With Quote
  #13  
Old 02-25-2019, 09:42 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

Indirect works
thanks to aal for efforts
R
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
accessing individual row data in named ranges MimiCush Excel 25 09-27-2018 01:01 PM
Named Ranges Help SavGDK Excel 5 05-01-2017 09:41 AM
Data Validation List based on Two Named Ranges Rich18144 Excel 4 01-14-2016 04:37 AM
How to use named ranges in excel vba? bosve73 Excel Programming 4 01-25-2012 09:26 AM
Dynamic Named Ranges using text hannu Excel 0 06-22-2010 04:42 PM


All times are GMT -7. The time now is 05:24 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft