Getting a formula to reflect added columns

[alittlehelp]

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

Thanks,
Chri

 

[GS]

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

 

[Ron Rosenfeld]

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

 

[GS]

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

 

[GS]

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

 

[GS]

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

 

[papbab]

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

 

[Ron Rosenfeld]

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.

 

[GS]

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

 

[GS]

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

 

[GS]

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