Indirect…?

[How_Do_I]

Hello all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have my data set out in the format below:

Excel Workbook
	B	C	D	E	F	G	H	I	J
4	2009	-	2010		2008	-	2009		Order
5	Home09	Away09	Date09		Home08	Away08	Date08		2009 - 2010
6	Data	Data	Data		Data1	Data1	Data1		2008 - 2009
7	Data	Data	Data		Data1	Data1	Data1
Sheet1

<o> </o>
The highlighted cells are named ranges…
<o> </o>
I’ve used INDIRECT a lot in my workbook and it is slow to say the least…
<o> </o>
INDIRECT and other functions sorted my data by current year down to oldest year.
<o> </o>
So, when I add this years data I paste it over the oldest data, formulas will change the name in the highlighted cells so all I need to do is re-name the ranges to have my book work.
<o> </o>
<o>

Excel Workbook
	B	C	D	E	F	G	H	I	J
4	2009	-	2010		2010	-	2011		Order
5	Home09	Away09	Date09		Home10	Away10	Date10		2010 - 2011
6	Data	Data	Data		Data3	Data3	Data3		2009 - 2010
7	Data	Data	Data		Data3	Data3	Data3
Sheet1

</o>
<o></o>
Can anyone see a way of doing this without INDIRECT please… I was thinking perhaps CHOOSE but I just can think how to get this started…

 

[jasonb75]

You forgot to post the existing formula

 

[How_Do_I]

I didn't forget to post them Jason, I was lokking for ideas or alternative methods. I wouldn't know which formula involving INDIRECT to send, my book is full of the them...

Shall I sent you a copy of this book so you get a better idea?

 

[jasonb75]

Just a couple of examples would give an idea, sometimes INDIRECT can be replaced with something more efficient, but not always, it depends how it's being used.

As an alternative to changing a load of formula, have you considered manual calculation?

 

[Jonmo1]

I was lokking for ideas or alternative methods

To do what exactly?
You're description is a bit vague to say the least.

You posted 2 screenshots, but they don't appear to be Before and After..

Please show a screenshot of the Source Data and a Screenshot of your expected formula results.

 

[How_Do_I]

Jason here are some formulas from one of my sheets...

I use the last two digits of a year to "title my ranges"...

Then formulas use INDIRECT to get the data.

Excel Workbook
	F
3	Season
4	2009 - 2010
5
6	Code
7	09
8	10
All Seasons

Excel Workbook
	C
26	Watford
27	WBA
28
29	Home09
All Seasons

#VALUE!

</td></tr></table></td></tr></table>

 

[How_Do_I]

Other examples include...

Excel Workbook
	M	N	O	P	Q
12	Home By League Position	2009 - 2010	W	D	L
13	1	Newcastle United	18	5	0
Home Wins etc

[/B]

 

[Jonmo1]

It looks like you're building "Named Ranges" in certain cells, and Indirect is referring to those named ranges...

If so, I don't see any other way to do that specifically.


Generally speaking, if you need to build sheetnames or named ranges with variables, then Indirect is the only way without VBA.

 

[jasonb75]

Who provided the named range formula?

Looks like one of Aladin's, so chance of finding a more efficient non VBA solution will be somewhere between extremely remote and non-existant.

 

[How_Do_I]

No idea where I found the formula. However, I am now using one that Aladin definitely gave me to extract a unique list alphabetically. I've also dropped the naming of ranges by year i.e. Home09, Home08 and just called that range Home1 Home2...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I ended up using CHOOSE to get my data from the correct year block... An average time of 70 second to complete a full calculation has now been reduced to 14 seconds...with the removal of 11,175 instances of INDIRECT in my book!!! <o></o>