Indirect function with worksheet-level defined name

[gurs]

Hi all. I am trying to use the Indirect function to reference a Name that is specific to a worksheet, but am not getting the results I expected.

Here is my setup:
- Cell A1 contains the word "Apple".
- The name "Product1" is defined as $A1, so that no matter what cell I select in row 1, if I type "=Product1" it will return "Apple". The name "Product1" is only valid on this specific worksheet.
- Cell B1 contains the number "1".
- In Cell C1 the formula is =Indirect("Product"&B1).

I would expect the formula in C1 to yield the same result as if I had entered =Product1 in cell C1. But it doesn't. Instead of returning "Apple", it returns a zero. What am I missing?

This problem only exists for names that are not tied to a specific cell. So if I change the definition of "Product1" to $A$1, then the formula in C1 works fine. But it only works in row 1, of course, which is not what I need.

Thanks in advance for any assistance.

[djapigo]

Hi gurs,

Sorry I can't seem to replicate your problem... the indirect function seems to work fine with my spreadsheet...

What version of Excel do you have?

If you can, please add a workbook for us to look at. (click on Go Advanced and look for a paperclip)

Thanks,
Dennis

[gurs]

Okay, I have uploaded a sample file. This file shows the results of the indirect formula with four different methods of defining the name.

Row 4 defines the name as =Sheet1!$A1 for the worksheet only;
Row 5 defines the name as =Sheet1!$A$2 for the worksheet only;
Row 6 defines the name as =Sheet1!$A3 for the entire workbook; and
Row 7 defines the name as =Sheet1!$A$4 for the entire workbook.

The indirect formula works if the name is valid for the entire workbook, but not if it is only valid for the worksheet.

I am running Excel 2013 x86 on a Win7 Ultimate x64 machine. Thanks for the help!

[martindwilson]

hmmmmmmmmmmmmmmmm
works fine for me too

[djapigo]

Hi gurs,

If you look at the Name Manager, your defined names are not referenced to the correct cells.

Fix this and you will see the formulas produce your desired results.

Let us know if this still doesn't fix this.

Dennis

[gurs]

That is bizarre. Those formulas all changed at some point after I entered them. Anyway...

Simplified version attached (Indirect_Test2). The name "Product1" is defined as the value in Column A and the active row (for the worksheet only). Why doesn't the indirect formula in D1 work?

[djapigo]

Hi gurs,

Is there a reason why you need the name "Product1" referred to $A4 where you don't have absolute referencing on the row?

In other words, if you change the reference to $A$4, then everything will be ok.

I thought at one point, absolute referencing didn't matter in naming ranges, but it looks like it's now needed.

I guess Excel needs a fixed range for naming ranges. Which if you think about it, kinda makes sense that it should not change.

Anyway, change it to $A$4 and everything should be ok.

re-post: after a quick search, I found this website... this is total news to me...
http://www.jkp-ads.com/articles/ExcelNames03.asp

and another
http://faculty.ucc.edu/business-gree...ngAbsolute.htm

[gurs]

Is there a reason why you need the name "Product1" referred to $A4 where you don't have absolute referencing on the row?

The reason I need Product1 to be relative is that I need to be able to fill the formula down column C, D Q or whatever. I need "Product1" to return whatever is in column A of the given row. I don't think there is any way to do that if I use absolute referencing, is there?

[djapigo]

I see... well, I'm at a loss... sorry, but you have a very interesting problem that is beyond me... hopefully someone else has an idea...

If nothing else, maybe we can attack this problem a different way, what exactly are you trying to accomplish?

[gurs]

If nothing else, maybe we can attack this problem a different way, what exactly are you trying to accomplish?

My real use of this formula is more like this: Column A contains a list of products in A1:A1000. Cells B1:B1000 contain the prices offered by Company1 for those products, Cells C1:C1000 contain the surcharges charged by Company1 for those products, and Cells D1:D1000 contain the discount offered by Company1 for those products. There are many companies, each taking up three columns. Each of the columns is defined exclusively to the worksheet like this: Company1Price=$B1. Once I know the best price, I can deduce the number of the Company formulaically. From there, I want to be able to reference the other information. Using Indirect, I could do that by using a formula like this: =Indirect("Company"&<formula containing company number>&"Discount"). This is still a simplified example, but closer to reality. Does that help?

[gurs]

<withdrawn>

[gurs]

Okay, enough with the simplified examples. Here is the real workbook. Hope it's not too confusing. Take a look at cell S7. It contains an example of how I'd like to use the Indirect function. It should be working, but it isn't. This will be filled down through row 29, so it can't be an absolute reference. There are many more functions that I was planning to build like this, so any workaround has to be scalable. Also, I can't use any VBA because I need to be able to access this via Excel on the Web. Any ideas?

[djapigo]

How about this formula instead? Does it get what you want?

=INDEX($AO$5:$BQ$29,ROW(A7)-4,MATCH(L7,$AO$5:$BQ$5,0)+1)

[dredwolf]

Just a side note...you said your using excell 2013...is it a beta,RC or ???...it may not be completely debugged yet, and hence some problems...just a thought..

[JosephP]

the issue is that the use of indirect removes any context for the evaluation of the named range so you always get row 1. you either need a different formula-such as the one suggested earlier-or you can define the named ranges to refer to entire columns

[gurs]

djapigo - Thanks for the formula. I will give it a shot and let you know how it goes.

JosephP - Thanks for the explanation. It's too bad there isn't a way to have the Indirect function use its own location for context. After all, the idea with Indirect is that you are supposed to get the same value as if you had directly typed the formula in the cell. So it would make sense that the cell containing the Indirect formula would be the anchoring cell.