Define name formula

**matrixknow** · 06-14-2007, 03:46 AM

I found some example on how to name a range to get a Dynamic Data Source for my Pivot Tables. However he said that on trying to run this formula in Insert > Name > Define is not ok. I copied the exact formula. What is wrong with my syntax ?

Please Login or Register  to view this content.

**arthurbr** · 06-14-2007, 04:10 AM

Originally Posted by matrixknow

I found some example on how to name a range to get a Dynamic Data Source for my Pivot Tables. However he said that on trying to run this formula in Insert > Name > Define is not ok. I copied the exact formula. What is wrong with my syntax ?

Please Login or Register  to view this content.

Eliminate the space between COUNT and A

It should be CountA

**matrixknow** · 06-14-2007, 04:43 AM

ok that was one problem that I fixed on your proposal
but another issue was that I need to replace , (comma) through ;
your postscript give me this idea.

**matrixknow** · 06-14-2007, 05:09 AM

If I create a Pivot Table on this in the same workbook he can base this on the define named range.

When I try to base my existing or new Pivot Tables in another workbook I got a problem, the error said "invalid reference".

original not named reference works :

Please Login or Register  to view this content.

reference to the named range is invalid :

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**Richard Schollar** · 06-14-2007, 06:25 AM

Does it work with simply:

REPORT_BASIS.xls!Database

**matrixknow** · 06-14-2007, 06:54 AM

sorry, no, this was my first trial.

I tested it again. In the same workbook you just need as reference for the range name "Database". It works perfectly. From another workbook I need to include the path or at least the file name where the Database named range is in and he always mentioned "reference invalid".

**arthurbr** · 06-14-2007, 08:48 AM

If you range is on a sheet called sheet_1
try

Please Login or Register  to view this content.

**arthurbr** · 06-14-2007, 08:50 AM

And BTW, it would be nice to start a new thread when you have another question, so everybody can profit from the information

**matrixknow** · 06-14-2007, 09:23 AM

1. the same error "reference is not valid" I don't see, path and file and named range are correct. Perhaps Database is a reserved word, but I uses also Database1 and I have seen this in the example code

2. I could start a new thread as I did yesterday with listbox question. These where two different question, so I make two of it. It seemed to be a violation of the rules and they where merged. Its ok to have rules. Its not ok to use two rules which are exactly the opposite of each other.

3. Its nice info to see where your help comes from, eg. Austalia, UK and .. Belgium. Can we adapt, add this in my profile. Location : Belgium. how can I do this ?