Dynamic Range - (something simple)

[buz]

This is hard to ask, but for some reason my dynamic range quit working. The Data>Form does not add to the range.

I have deleted the range, reselected it, renamed it and then rebuilt my PT without success. I have used diff names for it. I have opened the wizard in a PT, gone back to step two to reselect the range and still nothing. I have used the wizard to 'name' the range and nothing. I have added data (via Data>Form) to the DB, reselected the DB to include the new row, but when I select the name from the name box only the 'old' range is selected.

What am I missing

tfyh

Buz

 

[Damon Ostrander]

Hi Buz,

What is the formula associated with your Dynamic Range name?

Damon

 

[lenze]

Using the Data>Form will expand a range named database when new entries are entered. You must, HOWEVER, use the form always. If you manually enter a record by typing or pasting in a row, you will break the chain and the range will no longer be dynamic. One fix is to find and remove the row(s) that where added manually. A better fix, is to do what Damon is hinting at. Use a formula to define your dynamic named range. Several methods have been posted. Do a search for Dynamic Named Range. Aladin, I know has posted these formulas sevearl times.

HTH

 

[buz]

Thank you. I have seen the formula method but figured I didn't need it cos of xl's shortcut method - naming the range and off you go. It has worked for months this way.

If I manually put in a row of data, then manually delete it - shouldn't I still be in the dynamic. Or does this 'break' it?

I would like to use it as I have been with the Data>Form method and I do not believe there is any data in the DB which was manually inserted. When I select it's name from the name box the DB (about 150 entries) shows up selected - wouldn't that mean it is 'unbroken'?

Using the formula method - would I still use the Data>Form method of updating the DB? What does using the formula method 'mean'?

I may even consider re-doing my DB (about 150 entries) if it meant I could continue using it without the formula method.

I'm not sure what my resistance is to the formula method. Is one way or the other better?

tfyh

Buz

 

[PaddyD]

Is one way or the other better?

Depends what you're doing. How about this thought, though - using the 'xl shortcut' approach, your updating list & PT is bust. The dynamic range is agnostic about how records are entered. See

http://mrexcel.com/board/viewtopic.php?topic=20660&forum=2&start=10

for an example from Aladin re setting up dynamic ranges

Paddy

 

[buz]

Thank you Paddy - but ......... that ref is so far over my head makes me dizzy!

Your mention that my DB and PT is busted - do you mean - I'm up a creek with it? Maybe I should start over?

tfyh

Buz

 

[RichardS]

Buz,

Perhaps you could send someone your spreadsheet to look at. I know what you're on about, and it seems very strange that Excel is behaving this way.

Richard

EDIT:
Buz, I was just messing about with this to see what might be causing it. The same thing is happening with me. I thought you could name your list anything, and using the Excel built in Data Form would extend the named range, but apparently not. It appears you need to name the range Database for this feature to work.
_________________
This message was edited by RichardS on 2002-09-05 22:05

 

[buz]

I would be more than happy to have some one look at this wkbk for me. any offers?

I've spent days trying to get it back on track and going nowhere fast, not to mention falling behind in maintaining the DB.

tfyh

buz

 

[RichardS]

See edit above. As lenze poined out earlier, range must be named

Database

Richard

 

[buz]

Richard - honestly I thought I had crossed this option off the list as a possible solution, meaning - tried to rename the range "database" and still it didn't work.

Long story short .... it does work. I have read miss leading opinion regarding this - some say you can name it anything and maybe that is true if you use the formula method.

thank you thank you thank you

Buz