Dynamic Range - (something simple)

buz

Board Regular
Joined
May 30, 2002
Messages
230
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
May 30, 2002
Messages
230
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

Board Regular
Joined
May 30, 2002
Messages
230
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

Well-known Member
Joined
Feb 16, 2002
Messages
761

ADVERTISEMENT

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

Board Regular
Joined
May 30, 2002
Messages
230
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

Well-known Member
Joined
Feb 16, 2002
Messages
761
See edit above. As lenze poined out earlier, range must be named

Database

Richard
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
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
 

Forum statistics

Threads
1,144,293
Messages
5,723,554
Members
422,503
Latest member
aarifmahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top