YTLE#58: Data Validation & Excel 2007 Tables

Hi friends!,

I have a data file, and another file with some pivot tables on different worksheets, each reading from the data file.

I am trying to write some code in the data file that tells the relevant pivot table to refresh anytime data changes in the source table. What I wrote doesn't work. I have pasted a sample of what i came up with, below.



Private Sub Worksheet_Change(ByVal Target As Range)
[file_name.xlsm] worksheet_name!.PivotTables("PivotTable1").RefreshTable
End Sub



Could anyone help me correct this code, or give me a better way to do this?

As always, I am very grateful for any ideas and suggestions.

I've created several table queries in Access 2007 that run as expected. In Excel 2007 under the data tab you have the option to "Get External Data" "From Access". In most cases I would select the database and see a list of tables to link to the worksheet. I've created 4 today that I can see in Access that don't show up in my options for Excel. The list of tables doesn't give an option to refresh and I've tried exiting both programs and starting over. Any ideas?

-goodluck

Is there a way to find out valid values of a filter in a data table inside a macro?

I find out what values of say "Product Codes" are in the "Product Code" filter. These values are not always the same, as they often change whenever new data is extracted from the database. Due to design limits I cannot use pivot table, hence I must rely on using data table.

I appreciate your help.
Thanks,
Anil

I have a chart with 1 line and 1 bar, and a data table at the bottom with the data for both the line and the bar. I'd like to keep the graph unchanged, but remove the data for the bars in the data table leaving one row with the data for the line.

Hi

I have inserted a Data Table at the bottom of my chart. The graph is a column graph, rolling 12 month data, there are 4 categories in each month, so the chart will always show 48 plotting points.
My data table at the bottom is reversing the order of values, yet the data table itself shows the months in the correct order. Does anyone know a workaround for this.
Thanks
Craig

I am working with an Excel file (file about 4Mb, using Excel 2007) with various complex calculations and charts. Amongst others, there are 20 data tables in the file on a hidden sheet.

Anyhow, the problem is that the calculations do not happen anymo I have one cell that I change, which should lead to the updating of all my 20 data tables:
-If I put everything to automatic calculations, the data tables will only update to nr 3-5 (seems random when the calculation will stop), leaving the other 15-17 data tables as they were.
-When I change my file to Manual Calculation and try to calculate, the same thing happens.

The weird thing is that the calculation is carried out perfectly (=every data table is recalculated) when I save the file...

Does anybody have an idea what could cause this?

I am trying to merge 2 tables of data in Excel 2007. They have about 500 columns each. Some of the columns are identical from one table to another but many columns are different. I am trying to merge these 2 different tables to product a single pivot table report.

Is there any simple way to do this? I need a process that is quick since I need to do this consolidation on a daily basis. If I did it manually it would take hours.

Any ideas?

Thanks!

I'm trying to set Data Validation in cells on one tab to a named range column on another tab, using the List format. I get the drop-down arrow that shows the list of valid entries, and the message box but, and this is a BIG BUT, I can also enter data that are not on the list. In fact, I can enter anything.

And yes, I did use the STOP style on the "Error Alert".

I'm doing exactly the same as in my old Excel 2000, but this $%^#&*% 2007 doesn't work right.

Barry

I have looked honest, is there a way to restrict my list to only items that aren't "".

My Data Validation List looks up a column which has the formula

=IF(AE5="","",AE5)

The length of the column can change, so I only want to bring back into my dropdown list, anything not ""

Can someone tell me how I do this please.

Hi

I have just been upgraded to Excel 2007 and whilst there are some very useful features I am somewhat dismayed that there are old features that are no longer supported.

Specifically, I want to create a data validation statement that allows the contents of a list and dynamically adjusts itself to the length of the list. In the good old days I created a Names Range, defining the range by =OFFSET($AA$1,0,0,COUNTA($AA:$AA),1) for example and then referenced that name in the data validation statement.

Looking at some of the blurb on the Excel pages it appears that Excel 2007 no longer supports named ranges. My question, therefore is twofold:

1 How can I achieve the same in Excel 2007, and

2 Can I save a workbook created in Excel 2007 and preserve this functionality for earlier versions?

Many thanks