#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Agree, but I have a dynamic number of subs and i’m Not intended to update the formula for each sub
|
#4
|
|||
|
|||
If you have A1:A5 named Sub_1 then you can enter in B1: =Sub_1+5 and copy down to B5.
|
#5
|
|||
|
|||
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
|
#6
|
|||
|
|||
="Sub_"&A1+5
|
#7
|
|||
|
|||
Doesn’t work in my sheet,
|
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
Sorry you’re right but I’m currently not at home, I’ll reply tomorrow what exactly is shown in the file,
|
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
Quote:
Code:
=indirect("sub_"&A1)+5 |
#12
|
|||
|
|||
Code:
=CHOOSE(MATCH($A$1,{1,2,3,4,5},0),sub_1,sub_2,sub_3,sub_4,sub_5) 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. |
#13
|
|||
|
|||
Indirect works
thanks to aal for efforts R |
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 |