Database list vs pivot

  • Thread starter Thread starter gabinettoski
  • Start date Start date
G

gabinettoski

Hallo everyone,

I am following a tutorial to do a simple database list in excel.
The tutorial suggest me to do Data>List but in Data menu in excel
2000 I don't have list!!!!
Someone could tall me how to create a list with Excel 2000. If I
select Pivot start a wizard that cover with a mask my data
Tnx
 
Data|List was added in xl2003 (IIRC).

It doesn't exist in xl2k.

Maybe you could use data|filter to see some of the features of data|list.
 
Hi

Data>List was introduced in XL2003, and replaced with Insert>Table in
XL2007.
The List created in Xl2003 is a dynamic range, that will grow as you add
more data to the source.

To create a Dynamic named range (in any version of Excel) you can use
Insert>Name>Define>
Name myData
Refers to =$A$1:INDEX($1:$66536,COUNTA($A:$A),COUNTA($1:$1))

In your Pivot Table, when you get to Source, enter = myData

How it works.
The above assumes that you have headings set up in row 1, with no gaps (that
would ne necessary for a Pivot table anyway, as all columns (fields) must
have headings)

COUNTA($A:$A) will return the number of rows that exist in the range lets
say the result is 100
COUNTA($1:$1) will return the number of columns used lets say the result is
8
INDEX($1:$65536,100,8) would Index the whole sheet and return a result of
H100
So the range myData would refer to $A$1:H100

If any more rows, or columns, are added to the source data, then the named
range myData will expand accordingly.
 
Hi

Data>List was introduced in XL2003, and replaced with Insert>Table in
XL2007.
The List created in Xl2003 is a dynamic range, that will grow as you add
more data to the source.

To create a Dynamic named range (in any version of Excel) you can use
Insert>Name>Define>
Name myData
Refers to =$A$1:INDEX($1:$66536,COUNTA($A:$A),COUNTA($1:$1))
Click to expand...

A gui tell me that the formula you have typed contain errors
Thanks for your interest
 
Hi

Data>List was introduced in XL2003, and replaced with Insert>Table in
XL2007.
The List created in Xl2003 is a dynamic range, that will grow as you add
more data to the source.

To create a Dynamic named range (in any version of Excel) you can use
Insert>Name>Define>
Name myData
Refers to =$A$1:INDEX($1:$66536,COUNTA($A:$A),COUNTA($1:$1))
Click to expand...

Maybe I have understood! Maybe Excel don't recognize the comma...
also when I try to have a list with validation in the source field
after I have selected data->Validation-> allow list, if in source I
type separated records with a comma (for instance
January,February,March) when I try to have a dropdown in the worksheet
I obtain ONLY one specific record "January, February, March". This
happens despite internet tutorial employ comma! This happens also if I
try to have separate coulor line, if I insert in "define to" the
formula =Mod(row(),2), Excel give me the same like error, so I thought
that the main problem is that Excel 2000 don't recognize the comma
sign.
 
Hi

Your Italian version probably uses the semicolon ; as the separator as
opposed to the comma , which is used in UK and US versions of Office.

Replace , with ; in all formulae I have given.
 
Hi

Your Italian version probably uses the semicolon ; as the separator as
opposed to the comma , which is used in UK and US versions of Office.

Replace , with ; in all formulae I have given.
Click to expand...

tnx;)
 
Back
Top