Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-24-2019, 07:13 AM
Intruder Intruder is offline Use of named ranges based on cell content Windows XP Use of named ranges based on cell content Office 2007
Advanced Beginner
Use of named ranges based on cell content
 
Join Date: Dec 2010
Posts: 59
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 Use of named ranges based on cell content Windows Vista Use of named ranges based on cell content Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
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 Use of named ranges based on cell content Windows XP Use of named ranges based on cell content Office 2007
Advanced Beginner
Use of named ranges based on cell content
 
Join Date: Dec 2010
Posts: 59
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 Use of named ranges based on cell content Windows 10 Use of named ranges based on cell content 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 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 Use of named ranges based on cell content Windows XP Use of named ranges based on cell content Office 2007
Advanced Beginner
Use of named ranges based on cell content
 
Join Date: Dec 2010
Posts: 59
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 Use of named ranges based on cell content Windows 10 Use of named ranges based on cell content 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

="Sub_"&A1+5
Reply With Quote
  #7  
Old 02-24-2019, 12:36 PM
Intruder Intruder is offline Use of named ranges based on cell content Windows XP Use of named ranges based on cell content Office 2007
Advanced Beginner
Use of named ranges based on cell content
 
Join Date: Dec 2010
Posts: 59
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 Use of named ranges based on cell content Windows 10 Use of named ranges based on cell content 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

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 Use of named ranges based on cell content Windows XP Use of named ranges based on cell content Office 2007
Advanced Beginner
Use of named ranges based on cell content
 
Join Date: Dec 2010
Posts: 59
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 Use of named ranges based on cell content Windows 10 Use of named ranges based on cell content 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

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, 7 views)
Reply With Quote
  #11  
Old 02-25-2019, 12:02 AM
alpha alpha is offline Use of named ranges based on cell content Windows 10 Use of named ranges based on cell content 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 Use of named ranges based on cell content Windows 8 Use of named ranges based on cell content Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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 Use of named ranges based on cell content Windows XP Use of named ranges based on cell content Office 2007
Advanced Beginner
Use of named ranges based on cell content
 
Join Date: Dec 2010
Posts: 59
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
Use of named ranges based on cell content Named Ranges Help SavGDK Excel 5 05-01-2017 09:41 AM
Use of named ranges based on cell content 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:54 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