VBA for updating rage of a name

dukeofscouts

Board Regular
Joined
Jan 19, 2009
Messages
146
ok long story short:
-I currently use a macro to insert date for an entry in to a database. Part of that entry is states what kind of entry it is. I use the sort function to sort the datebase by this field.
-I have a series of Pivot tables set up that show for each type, what entries are under that catgorie. Each time I run the macro to insert the new record. I have set that the pivot tables update and thus as the new record enters the database and is sorted to mach the type field, the corrisponding pivot table also shows a new entry.
- I have se that each pivot table list is a name. However, I would like to have the range expand as I enter new records automatically.

I've heard that there is a VBA code that will do this. Does any one happen to have it?

Thank you in advance,
DukeofScouts
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
not sure if this is what you are looking for but you can set each range so that it is dynamic (changes as you delete or add) in your named range set the refers to to something like this

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

change the $a to whatever column you are using
 
Upvote 0
That is true, and I did try that. The problem is in how I use the names. In the work book I use DV to pull up and compare records. I select the type of record I want then the I selct the record. To get the DV to work most effectivly I had to set one DV to be based on the other cell, via the "INDIRECT" function. Thus the order was this
B1 DV: Allow list; [types of records]
--Each record was:
----=OFFSET(Record!a2,0,0counta(a2:a100),1)
----=OFFSET(Record!B2,0,0counta(B2:a100),1)
(and so on)
B2 DV: Allow list; =INDIRECT(B1)

Well this would reslut in b2's list being blank. My guess is that excel can not use functions for DV based on another DV because that would require a calculation of two formulas at the same time, when they are not directly nested.

Thus I'm hoping for some VBA that will get me around the problem. Does that make it a little clearer:confused:

Thanks for the attmpt though, sorry I wasn't clear enough.
 
Upvote 0
That is true, and I did try that. The problem is in how I use the names. In the work book I use DV to pull up and compare records. I select the type of record I want then the I selct the record. To get the DV to work most effectivly I had to set one DV to be based on the other cell, via the "INDIRECT" function. Thus the order was this
B1 DV: Allow list; [types of records]
--Each record was:
----=OFFSET(Record!a2,0,0counta(a2:a100),1)
----=OFFSET(Record!B2,0,0counta(B2:a100),1)
(and so on)
B2 DV: Allow list; =INDIRECT(B1)

Well this would reslut in b2's list being blank. My guess is that excel can not use functions for DV based on another DV because that would require a calculation of two formulas at the same time, when they are not directly nested.

Thus I'm hoping for some VBA that will get me around the problem. Does that make it a little clearer:confused:

Thanks for the attmpt though, sorry I wasn't clear enough.

this might help you

http://www.youtube.com/watch?v=Los7Nb6Skdk&feature=channel_page
 
Upvote 0
I see that I have once again confussed you all.
Allow me to re-word this.

I have a DV based on another cells DV using the "INDIRECT" function and cell range names.

I tried to use the "OFFSET" function to create cell range names that were dynamic. However, I found that this does not work in conjunction with the "INDIRECT" function.

What I need is a way that will wokr with the "INDIRECT" function and will update the cell range names. I know there is a VBA to do this, but I do not know what the VBA code is.

does that clear it up?
 
Upvote 0
I see that I have once again confussed you all.
Allow me to re-word this.

I have a DV based on another cells DV using the "INDIRECT" function and cell range names.

I tried to use the "OFFSET" function to create cell range names that were dynamic. However, I found that this does not work in conjunction with the "INDIRECT" function.

What I need is a way that will wokr with the "INDIRECT" function and will update the cell range names. I know there is a VBA to do this, but I do not know what the VBA code is.

does that clear it up?


sorry cant help with the vba but that link i gave you shows you how to make dynamic ranges and use indirect... it does this by making tables which automatically adjust as you add or delete from them... this way you do not need to use the offset formula in your named ranges
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top