Defined Names - choose which one to use

[rabright]

Hi all, this is my first post - I've had a lot of help from this forum by lurking but couldn't find an answer to my specific question!

I'm working with spreadsheets of sales data, sorted by product. Each product is then split into quarters going back to Q1 2007 and has an array of clients and how much they paid us for each quarter. Each array has a defined name for each product (seven products total, so seven different workbooks, one for each product). Each workbook uses the same Name for each array, like this:

fullQ207 will refer to the array containing the amount each client paid us in Q2 2007 for the product in question.

I have a "main" workbook which pulls out the data for a specific client from each array using the MATCH/INDEX combination, and gives a sum for the past four quarters. So at the moment this is hard-coded to do Q3 2007 until Q2 2008. Obviously with Q3 2008 nearly over I will need to update this. I want to know if there is a way that for example I can type into 4 different cells the names I want to use and have Excel recognise that these cells are Names that I want it to use.

So for example I will have in the cells fullQ107, fullQ207, fullq307, and fullq407 if I want to see the data for the entire of 2007. How can I make my MATCH/INDEX equations use the contents of these cells as a Name rather than a value, and so know which external arrays to look in?

Hope that is clear enough but I will gladly clarify if required...

Richard

[reggie1000]

hello Richard,

I think what you are after is using INDIRECT within the formula, it allows you to "hardcode" a formula and references changable cells on a worksheet, have a look at the attached sheet, see if it is any good for what you need, if not then post an example.

thanks reg

[rabright]

Hi Reg, thanks for your help - that does look like it will do the trick.

I had a look at the INDIRECT function and saw that if it references to another workbook it will return a #REF error if the referenced workbook is not open. I intend on having Defined Names in the cells in the main workbook which reference Defined Names (the arrays) in the referenced workbook, so I assume the referenced workbook will need to be open to do this? And if so is there any workaround??

[reggie1000]

hi Richard,

I don't use closed workbooks for the stuff i do, but i have found a previous post that might help....

hopefully it may give you some insights in what you need.

thanks reg

[rabright]

Great thanks for your help Reg!