So frustrated. All I need to do is expand a simple range :(

[Arthur Pappas]

*I'm using Excel 2007*

1. Sheet2 cells A2 > A6 of my workbook constains a list (expense
categories). I selected these cells & typed EXPENSES in the name box.

2. Sheet1 is selected. I go to the 'Developer' tab, 'Insert', then I select
'Combo Box' (form controls). I place my Combo Box in C1. I right click the
Combo Box, select 'Format Control', then in the Iinput Range I type EXPENSES.

3. All works fine until I add another line to the EXPENSES list on Sheet2!!!

All I ask is one simple question:

How can I make the EXPENSES column grow/shrink to my needs & have that info
reflected in my Combo Box?

I am at my wits end & can't remember cursing so much since high school!!!

 

[carlo]

I never used 2007 but in 2003 i would create a dynamic range instead
of a fixed range.

Maybe this will help you:
http://www.ozgrid.com/forum/showthread.php?t=68869

hth

Carlo

 

[Arthur Pappas]

Carlo, I thank you for the link but I need a simple answer to my simple
question. That stuff is all way over my head with formulas, etc, etc.

 

[Max]

Imo, the simplest is still to re-define "Expenses" as a dynamic range

In xl2003, I'd just click Insert > Name > Define
select the name: Expenses
and overwrite whatever's within the "Refers to" box with say, this:
=OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A$2:$A$200))
Then just click OK, and I'm done.

The above will make Expenses a dynamic range which will shrink or expand as
items are deleted/added. The items' range is assumed continuous from A2 down,
w/o any blank cells in between.

---

 

[Don Guillett]

insert>name>define>name it>in the refers to box touch the f2 key and then
type in
=offset($a$2,0,0,counta($a:$a),1)
touch enter. Now the range is self-adjusting for additions and deletions.
If you want to make it so that if you delete a2 then use
=offset($a$1,1,0,counta($a:$a),1)

Merry Christmas from Texas

 

[Don Guillett]

If you want it to ignore blanks and find the LAST number use
=offset($a$2,0,0,match(9999999,$a:$a),1)
change the ,1 to ,4 if you want to include col a,b,c,d
or =offset($a$2,,,match(9999999,$a:$a))
for just that column
for text use "zzzzzzzzzz"
or any value larger than possible in your data

 

[Dana DeLouis]

Hi. Would this alternative work for you?
,
Go to Expense. I assume you have a Heading name in A1, and data in A2:A6,
with Column B clear. Also, delete your range name "Expenses" for now.
Select A1 and do Insert > Table. Select "My Table has headers."
As you add data, the table will grow. To Delete, right click, and select
"Delete - Table Row"

If you select inside the Table, you will notice a "Table Tools" menu in the
upper right of the menu bar.
Here, you will see the default table name of something like "Table1."

Select Table1 from the name box to select your data. Type a new name in the
Worksheet Name box, like "Expenses." I don't know why, but this works for
the next step.

Now, go to your Sheet1, and select Column C.
Select Data > Validation.
Under Settings > Allow, select List.
Under Source, hit the F3 button and select "Expenses"

(Notice there are no Table names here. I get errors when I try to reference
Tables. Maybe someone else can suggest a better way to use table names.)

What we have now is instead of a Combo box form, we are using Data -
Validation.
Your range name will grow as data is added to the bottom.
Play around with the options for Data Validation. You can give messages /
feedback to users here.

 

[Arthur Pappas]

Dana,

that seems to be working for me, thank you! One small problem: the newly
created data validation cells are blank & the drop down arrow does not appear
unless the cell is selected. I literally have to guess where the cell is &
then the drop arrow appears & I can make my selection.

Thoughts?

Cheers,

Arthur