I have a table of 70,000 rows and 10 columns in Excel 2007. It is linked to
an Access 2007 table. The Insert Table menu option is grayed out. I must be
missing a basic understanding and cannot find any applicable documentation on
this. Also, assuming I can get this functionality, can I create multiple
tables (e.g., single filtered columns that can be used in the x/y variables
for a line graph. I have multiple graphs that I would like to update from
this main table using named tables. Currently, having to use Pivot Tables
and creating associated datasets for use in the graphs. These data sets do
not update on a refresh of the PTs and have to be created each update cycle
(have over 150 graphs to update).

Thanks in advance,

J Austin

Hi J Austin,

It sounds like you are having trouble inserting a table in Excel 2007 due to the large size of your data set. One possible solution is to convert your data set into an Excel table format. Here are the steps to do so:

1. Select the entire data set by clicking on the top left cell and dragging to the bottom right cell.
2. Click on the "Insert" tab in the ribbon.
3. Click on the "Table" button in the "Tables" group.
4. In the "Create Table" dialog box, make sure the "My table has headers" checkbox is checked.
5. Click "OK".

This should convert your data set into an Excel table format, which will allow you to use the "Insert Table" menu option.

As for creating multiple tables from your main table, you can use the "Filter" feature to create filtered views of your data. Here are the steps to do so:

1. Click on any cell within your table.
2. Click on the "Data" tab in the ribbon.
3. Click on the "Filter" button in the "Sort & Filter" group.
4. Click on the drop-down arrow in the header of the column you want to filter.
5. Select the filter criteria you want to use.
6. Repeat steps 4-5 for any additional columns you want to filter.
7. Once you have applied all your filters, you can copy and paste the filtered data into a new sheet to create a new table.

You can also use the "Named Range" feature to create named tables from your filtered views. Here are the steps to do so:

1. Select the filtered data you want to name.
2. Click on the "Formulas" tab in the ribbon.
3. Click on the "Define Name" button in the "Defined Names" group.
4. In the "New Name" dialog box, enter a name for your table.
5. Click "OK".

You can now use this named range as a data source for your graphs.

Hi,

I for one need more info - for example, are you in one table when you are
trying to create the second table?

Can you create multiple tables? - sure. But if a range is a table then you
can't create a table from the table. But since it sounds like the table is
created from a connection to an Access Database, you can create a second
table by connecting to Access a second time, no problem.

I'm not sure what you mean by: "can I create multiple
tables (e.g., single filtered columns that can be used in the x/y variables
for a line graph. " What do you mean by this?


What do you mean by: "I have multiple graphs that I would like to update
from this main table using named tables." I suppose you could give the
table more than one name but its still the same table, so why give it
multiple names?

--
Cheers,
Shane Devenshire


"J Austin" wrote:

I have a table of 70,000 rows and 10 columns in Excel 2007. It is linked to
an Access 2007 table. The Insert Table menu option is grayed out. I must be
missing a basic understanding and cannot find any applicable documentation on
this. Also, assuming I can get this functionality, can I create multiple
tables (e.g., single filtered columns that can be used in the x/y variables
for a line graph. I have multiple graphs that I would like to update from
this main table using named tables. Currently, having to use Pivot Tables
and creating associated datasets for use in the graphs. These data sets do
not update on a refresh of the PTs and have to be created each update cycle
(have over 150 graphs to update).

Thanks in advance,

J Austin

Shane,

Thanks for the response. I've done a little more analysis to clarify. The
inability to "see" the insert/table function appears to be related to having
the link from my Excel sheet to the Access table. If I copy/paste values the
Excel table to another sheet the insert/table is now active. However, I
haven't found a way to have the second sheet actively linked so it would also
contain any updates in the original Access sheet. I have to "break" the link
(either from the menu or just pasting values). Once I have that sheet, I
want to be able to filter/sort to be able to pick out related x/y values for
line plots. None of the x/y pairs ranges would overlap. I'm trying to
"name" these pairs in tables (separate x and y for each pair) and use the
names in the "select data" form of the graph. I have 14 graphs with 4 x/y
pairs in each. I'm trying to get away from having to re-build the named
tables each time I update the original Access table. I suspect this may be
the case if I have to do a copy/paste of values from the linked sheet to the
un-linked sheet. I've tried to find the syntax of the table function to see
if I can include the sort/filter criteria in each, but haven't found anything
clear (at least to me). I did read somewhere that you can't use formulas in
the named table function.

I'm considering going back to my Access table and building the required x/y
pairs there, linking to a named data table in Excel and then using those
names in the select data for the graphs. Hopefully, the changing number of
rows with each update would be accommodated in the named tables of Excel.

Thanks again,

J Austin

"ShaneDevenshire" wrote:

Hi,

I for one need more info - for example, are you in one table when you are
trying to create the second table?

Can you create multiple tables? - sure. But if a range is a table then you
can't create a table from the table. But since it sounds like the table is
created from a connection to an Access Database, you can create a second
table by connecting to Access a second time, no problem.

I'm not sure what you mean by: "can I create multiple
tables (e.g., single filtered columns that can be used in the x/y variables
for a line graph. " What do you mean by this?


What do you mean by: "I have multiple graphs that I would like to update
from this main table using named tables." I suppose you could give the
table more than one name but its still the same table, so why give it
multiple names?

--
Cheers,
Shane Devenshire


"J Austin" wrote:

I have a table of 70,000 rows and 10 columns in Excel 2007. It is linked to
an Access 2007 table. The Insert Table menu option is grayed out. I must be
missing a basic understanding and cannot find any applicable documentation on
this. Also, assuming I can get this functionality, can I create multiple
tables (e.g., single filtered columns that can be used in the x/y variables
for a line graph. I have multiple graphs that I would like to update from
this main table using named tables. Currently, having to use Pivot Tables
and creating associated datasets for use in the graphs. These data sets do
not update on a refresh of the PTs and have to be created each update cycle
(have over 150 graphs to update).

Thanks in advance,

J Austin