?
Solved

Using INDIRECT with Dynamic Range Names

Posted on 2014-09-05
9
Medium Priority
?
280 Views
Last Modified: 2014-09-09
All,

I have the following formula in a spreadsheet for tracking my time bookings:

=IF(ISERROR(INDEX(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$66,2,FALSE)),ROW()-ROW($B$22),1)),"",IF(INDEX(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$66,2,FALSE)),ROW()-ROW($B$22),1)=0,"",INDEX(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$66,2,FALSE)),ROW()-ROW($B$22),1)))

This works fine and when copied down across 15 rows provides me with a list of charge codes extracted from a bigger list. I have the bigger list split into two sections so that I can change my bookings for alternate weeks. The list is split into two Named Ranges - "Week1" and "Week2". As you can see the formula uses an INDEX formula on the Named Ranges, Week1 or Week2 based on the VLOOKUP which checks the current date in another list where I have week ending dates allocated to a 1 or a 2.

The problem comes when from time to time I have to add/delete booking codes to the big list; I then have to manually change the Named Ranges so that they still accommodate half of the list each, assuming even number of codes so that it can split evenly.

What I have tried doing but have been unsuccessful is using a Dynamic Named Range for Week1 and Week2. I have tried in a couple of ways:

1) Using an OFFSET formula within the "Refers to" to generate the range
2) Create the Range Name within a cell using ADDRESS and extra characters to make the full range; then use INDIRECT in the Name Manager to reference that cell.

Neither of these work.

What else can I try?

Thanks
Rob Henson
0
Comment
Question by:Rob Henson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40305548
Could you send a dummy?
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 40309666
See attached with relevant areas highlighted on Charge Lines tab.

Formula in column B also has another issue. I am attempting to spread my time evenly over a number of Charge lines. As you will see with this example, the last entry gets a bigger proportion. I suspect it is because I am dividing the time by a count of items in the range but the blanks at the bottom are getting included in the count; until you reach the last entry where it is taking the outstanding balance. I have tried changing to use COUNTIF(Range,"<>"&"") but that is still not working.

Any suggestions?

Thanks
Rob H
Dummy-Time-bookings.xlsm
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 40309680
Sorted the last issue:

(COUNTA($B$23:$B$42)-COUNTIF($B$23:$B$42,"")+1)

So just the original INDIRECT issue now to resolve.

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40310275
Hi,

if Week1Range and Week2Rang as required you could try in your formula

INDIRECT(INDIRECT(Week1Range))
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 40310384
Currently in Week2 so tried this:

=IF(ISERROR(INDEX(INDIRECT(INDIRECT(Week2Range)),ROW()-ROW($B$22),1)),"",IF(INDEX(INDIRECT(INDIRECT(Week2Range)),ROW()-ROW($B$22),1)=0,"",INDEX(INDIRECT(INDIRECT(Week2Range)),ROW()-ROW($B$22),1)))

I get blank so I guess it is causing an error so tripping the ISERROR

However, with

INDEX(INDIRECT(Week2Range)...

I get the right result. I guess that was a typo with the INDIRECT twice.

Thanks
Rob H
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 40311657
Sorry, the double INDIRECT was correct, the following now working:

=IF(ISERROR(INDEX(INDIRECT(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$46,2,FALSE)&"Range")),ROW()-ROW($B$22),1)),"",IF(INDEX(INDIRECT(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$46,2,FALSE)&"Range")),ROW()-ROW($B$22),1)=0,"",INDEX(INDIRECT(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$46,2,FALSE)&"Range")),ROW()-ROW($B$22),1)))

As an aside, any suggestions for making shorter?

Thanks
Rob H
0
 
LVL 33

Author Closing Comment

by:Rob Henson
ID: 40311663
Thanks for your help!
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40311725
Im not sure why you have to test for Index = 0

=IFERROR(IF(INDEX(INDIRECT(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$46,2,FALSE)&"Range")),ROW()-ROW($B$22),1)=0,NA(),INDEX(INDIRECT(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$46,2,FALSE)&"Range")),ROW()-ROW($B$22),1)),"")

if Not
=IFERROR(INDEX(INDIRECT(INDIRECT("Week"&VLOOKUP($D$1,$AD$1:$AE$46,2,FALSE)&"Range")),ROW()-ROW($B$22),1),"")

Regards
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 40311748
Thanks again, first suggestion works great.

I am testing for INDEX = 0 for the occasions when there is an odd number of Charge Lines so weeks 1 & 2 will have differing numbers.

Converting to blank is then allowed for in the spread of time calculations.

Thanks
Rob H
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question