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)...

[TABLE="width: 209"]
<TBODY>[TR]
[TD="class: xl65, width: 174"]DESCRIPTION
[/TD]
[TD="class: xl66, width: 104"]COST
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Cleaning
[/TD]
[TD="class: xl64, width: 104"]200.58
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Travel Time
[/TD]
[TD="class: xl64, width: 104"]489.56
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Wet Time
[/TD]
[TD="class: xl64, width: 104"]55.00
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Set- UP
[/TD]
[TD="class: xl64, width: 104"]855.00
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Operating Equipment
[/TD]
[TD="class: xl64, width: 104"]780.00
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Spotter
[/TD]
[TD="class: xl64, width: 104"]200.00
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Supervision
[/TD]
[TD="class: xl64, width: 104"]1200.00
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 174"]Plumbing
[/TD]
[TD="class: xl68, width: 104"]200.58
[/TD]
[/TR]
</TBODY>[/TABLE]

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)...

[TABLE="width: 209"]
<tbody>[TR]
[TD="class: xl65, width: 174"]DESCRIPTION[/TD]
[TD="class: xl66, width: 104"]COST[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Cleaning[/TD]
[TD="class: xl64, width: 104"]200.58[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Travel Time[/TD]
[TD="class: xl64, width: 104"]489.56[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Wet Time[/TD]
[TD="class: xl64, width: 104"]55.00[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Set- UP[/TD]
[TD="class: xl64, width: 104"]855.00[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Operating Equipment[/TD]
[TD="class: xl64, width: 104"]780.00[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Spotter[/TD]
[TD="class: xl64, width: 104"]200.00[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174"]Supervision[/TD]
[TD="class: xl64, width: 104"]1200.00[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 174"]Plumbing[/TD]
[TD="class: xl68, width: 104"]200.58[/TD]
[/TR]
</tbody>[/TABLE]

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.