(Msg. 1) Posted: Wed Aug 06, 2008 12:46 pm
Post subject: Formulas/References Add to elertz Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I have a spreadsheet where I add a new column every week and therefore have
to change the formulas every week too. The new column is inserted in column
H and everthing else is shifted down. I am having a problem finding a
formula or reference that will allow me to keep the following formula
constant.
=SUM($BH$5:CL5) which is in cell F5
When I add the column it shifts to BI5:CM5. I want the $B$H5 to stay and
the CL5 to continue to shift down every time I add another column. Relative
or absolute references do not make a difference as they change when a new
column is added. Anybody got any suggestions.
(Msg. 2) Posted: Wed Aug 06, 2008 12:59 pm
Post subject: RE: Formulas/References Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Extend your formula to extend one column after your current formula and shade
it grey to mark it. The next week, insert your column between the last
column of data and your grey-shaded column, and the formulas should
automatically adjust.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips
"Josh" wrote:
> I have a spreadsheet where I add a new column every week and therefore have
> to change the formulas every week too. The new column is inserted in column
> H and everthing else is shifted down. I am having a problem finding a
> formula or reference that will allow me to keep the following formula
> constant.
>
> =SUM($BH$5:CL5) which is in cell F5
>
> When I add the column it shifts to BI5:CM5. I want the $B$H5 to stay and
> the CL5 to continue to shift down every time I add another column. Relative
> or absolute references do not make a difference as they change when a new
> column is added. Anybody got any suggestions.
(Msg. 3) Posted: Wed Aug 06, 2008 1:04 pm
Post subject: RE: Formulas/References Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
There are other formulas in the cell adjacent to where the problem formula is
located.
"M Kan" wrote:
> Extend your formula to extend one column after your current formula and shade
> it grey to mark it. The next week, insert your column between the last
> column of data and your grey-shaded column, and the formulas should
> automatically adjust.
> --
> Tips for Excel, Word, PowerPoint and Other Applications
> http://www.kan.org/tips >
>
> "Josh" wrote:
>
> > I have a spreadsheet where I add a new column every week and therefore have
> > to change the formulas every week too. The new column is inserted in column
> > H and everthing else is shifted down. I am having a problem finding a
> > formula or reference that will allow me to keep the following formula
> > constant.
> >
> > =SUM($BH$5:CL5) which is in cell F5
> >
> > When I add the column it shifts to BI5:CM5. I want the $B$H5 to stay and
> > the CL5 to continue to shift down every time I add another column. Relative
> > or absolute references do not make a difference as they change when a new
> > column is added. Anybody got any suggestions.
(Msg. 4) Posted: Wed Aug 06, 2008 8:06 pm
Post subject: Re: Formulas/References Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Wed, 6 Aug 2008 12:46:01 -0700, Josh
<Josh RemoveThis @discussions.microsoft.com> wrote:
>I have a spreadsheet where I add a new column every week and therefore have
>to change the formulas every week too. The new column is inserted in column
>H and everthing else is shifted down. I am having a problem finding a
>formula or reference that will allow me to keep the following formula
>constant.
>
>=SUM($BH$5:CL5) which is in cell F5
>
>When I add the column it shifts to BI5:CM5. I want the $B$H5 to stay and
>the CL5 to continue to shift down every time I add another column. Relative
>or absolute references do not make a difference as they change when a new
>column is added. Anybody got any suggestions.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office