Named Range Reference Trouble

[brohymn62]

Hello,

I have several Named Tables set up which sort information from other tables into various sorts. I am refencing a specific column from these tables in an array formula which I am using to create a drop down list... for some reason my formula isn't working right... When I use Offset with regular range references it works fine, but when I use the structured references my formula breaks down. Any ideas? Here are the two formulas

WORKING FORMULA:

=OFFSET(COMMON!A2:A500,1,0,COUNTIF(COMMON!$A1:$A500,"<>0"))

BROKEN FORMULA:

=OFFSET(Table750[DESCRIPTION],1,0,COUNTIF(COMMON!$A1:$A500,"<>0"))

USING: EXCEL 2007

 

[RoryA]

What do you mean by "breaks down" exactly?

 

[Aladin Akyurek]

Excel Tables are already dynamic, so there is no need for invoking OFFSET...

Let's have the following Excel Table (created with Insert | Table)...

DESCRIPTION	COST
Cleaning	200.58
Travel Time	489.56
Wet Time	55.00
Set- UP	855.00
Operating Equipment	780.00
Spotter	200.00
Supervision	1200.00
Plumbing	200.58

<TBODY>
</TBODY>

If there are no blanks in the DESCRIPTION column, define DescList using Formulas | Name Manager as referring to:

=Table1[DESCRIPTION]

Now you can use DescList as Source in any data validated cell in order to obtain a dropdown list.

 

[brohymn62]

Excel Tables are already dynamic, so there is no need for invoking OFFSET...

Let's have the following Excel Table (created with Insert | Table)...

DESCRIPTION	COST
Cleaning	200.58
Travel Time	489.56
Wet Time	55.00
Set- UP	855.00
Operating Equipment	780.00
Spotter	200.00
Supervision	1200.00
Plumbing	200.58

<tbody>
</tbody>

If there are no blanks in the DESCRIPTION column, define DescList using Formulas | Name Manager as referring to:

=Table1[DESCRIPTION]

Now you can use DescList as Source in any data validated cell in order to obtain a dropdown list.

Thank you for responding, You are correct in your statement, but the reason I am using offset is because my table is popullated by dynamic information so there are several rows at the bottom which I don't want to show up in the drop down menu, The offset formula is supposed to alleviate this but only supplying the range with active information. Perhaps I am approaching this problem the wrong but I have been unsuccessful at getting Data Validation to understand any of my formulas.

 

[Aladin Akyurek]

Thank you for responding, You are correct in your statement, but the reason I am using offset is because my table is popullated by dynamic information so there are several rows at the bottom which I don't want to show up in the drop down menu, The offset formula is supposed to alleviate this but only supplying the range with active information. Perhaps I am approaching this problem the wrong but I have been unsuccessful at getting Data Validation to understand any of my formulas.

You need to make explicit what you want to exclude. The COUNTIF(COMMON!$A1:$A500,"<>0") doesn't make that clear.