So frustrated. All I need to do is expand a simple range :(
Arthur Pappas
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
of a fixed range.
Maybe this will help you:
http://www.ozgrid.com/forum/showthread.php?t=68869
hth
Carlo
On Dec 25, 5:29 pm, Arthur Pappas
Arthur Pappas
question. That stuff is all way over my head with formulas, etc, etc.
Max
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Don Guillett
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
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Arthur Pappas" <Arthur...@discussions.microsoft.com> wrote in message
news:7AB92933-C78F-471C...@microsoft.com...
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"Don Guillett" <dguil...@austin.rr.com> wrote in message
news:%23W9GrKv...@TK2MSFTNGP05.phx.gbl...
Dana DeLouis
,
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.
--
HTH
Dana DeLouis
"Arthur Pappas" <Arthur...@discussions.microsoft.com> wrote in message
news:7AB92933-C78F-471C...@microsoft.com...
Arthur Pappas
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