Hi,
Question for all the excel gurus. Let's say column A will be titled Total to date for all Periods and periods will be represented in the columns to the right of it, Column B will be titled previous total, Column C-F will represent Periods 1-4, column G will be the new period 5 I am adding. I have a formula for column A that reflects columns C-F or period 1-4 and the new period I am adding in column G. I know how to have my formula change automatically in column A when I add period 5 or column G. However, is there a way to get the formula in Column B to change automatically to reflect when I add the next period 6 or Column H? Thus column B which is previous total will now reflect columns C-G or period 1-5 and column H or period 6 will be the new column? Total to date will reflect columns C-H or periods 1-6. Any suggestions?

Thanks,
Chris

Use dynamic defined name ranges. Ref these in your formulas instead of
cell refs. Here's a good place to start...

http://www.contextures.com/xlNames01.html#Dynamic

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Mon, 27 Jan 2014 23:08:19 -0500, GS wrote:

Use dynamic defined name ranges. Ref these in your formulas instead of
cell refs. Here's a good place to start...

http://www.contextures.com/xlNames01.html#Dynamic

--

Hi GS,

Thanks for the link.
I've never used the method she describes for Excel 2007 of using a named table. I've always used the OFFSET method.
Do you have any idea how the Table method compares, with regard to workbook overhead, to using the volatile OFFSET method?

-- Ron

On Mon, 27 Jan 2014 23:08:19 -0500, GS wrote:

Use dynamic defined name ranges. Ref these in your formulas instead
of cell refs. Here's a good place to start...

http://www.contextures.com/xlNames01.html#Dynamic

--

Hi GS,

Thanks for the link.
I've never used the method she describes for Excel 2007 of using a
named table. I've always used the OFFSET method. Do you have any
idea how the Table method compares, with regard to workbook overhead,
to using the volatile OFFSET method?

-- Ron

@Ron
I'm not sure what you mean, Ron. I didn't follow the link because I
assumed it still explains how to create dynamic named ranges. IMO,
using Offset() requires tracking when cols are added/deleted in order
to specify position. A dynamic range auto-adjusts for this and so no
tracking required.

Perhaps, though, I misunderstand the OP's intent!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Just did a reading of the webpage I posted the link to. Clearly it has
been updated to include much more since I last read it some years back.
I see your point now with respect to a table [object], where I was
first thinking you meant a defined area (n rows by n cols) that was
named.

As for workbook overhead, my understanding/experience with table
objects is that they are (or can easily be) overhead intensive! Most of
my work with data sets involves 'in memory' processing either via ADODB
or VBA arrays. I normally use worksheets as database tables to display
content stored in plain text files. (The files may or may not be
encrypted)

Where data needs to be manipulated via criteria at runtime, I find a
multi-column listbox in a userform the easiest (low overhead) approach.
Because individual fields can't be selected I use a combobox control to
dynamically update when a record is selected so users can access the
individual fields. I find most users like this as opposed to using the
built-in dataform, because they like to see the data laid out in a
grid-like display. Optionally, I do have the fpSread.ocx ( a 3rd party
ActiveX spreadsheet) which works well as a data control also (one of
several other advantageous features).

Thus, my work with pivot/named tables is fairly minimal, but most of my
clients complain about how slow named tables are. Makes sense, though,
given the number of properties/methods attached! Defined ares don't
have that overhead...<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Typo!

...Defined areas don't have that overhead...<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

About multi-column listbox it can take a while until the data loads into it. Is this not the case for you GS?

On Tue, 28 Jan 2014 09:49:10 -0500, GS wrote:

IMO,
using Offset() requires tracking when cols are added/deleted in order
to specify position.

Well, one could do a count or counta for the width of the range, so long as the columns are contiguous. Although I am more likely to use single column dynamic ranges with the OFFSET method.

About multi-column listbox it can take a while until the data loads
into it. Is this not the case for you GS?

No! I dump the entire list in one shot rather than add each item...

Private Sub UserForm_Initialize()
Dim vData
vData = Range("$A$1:$E$10")
With Me.ListBox1
.ColumnCount = UBound(vData, 2)
.List = vData
End With
End Sub

...where the contents of a 10-row x 5-col set of data is now listed in
the control in one shot.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Shorter version...

Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = Range("$A$1:$E$10").Columns.Count
.List = Range("$A$1:$E$10").Value
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tue, 28 Jan 2014 09:49:10 -0500, GS wrote:

IMO,
using Offset() requires tracking when cols are added/deleted in
order to specify position.

Well, one could do a count or counta for the width of the range, so
long as the columns are contiguous. Although I am more likely to use
single column dynamic ranges with the OFFSET method.

Actually, a n-row x n-col table works fine too IMO!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion