B
BP
Using Excel 97 on Windows XP. (This workbook was originally created in Excel
4.0.)
I use a large excel workbook to do construction estimating.
The WB consists of a main sheet that contains BUTTONS that were created and
assigned to macros on a master macro sheet in the WB. The buttons take you
to the specific section of another worksheet that contains the cells that
return the total on the row corresponding to the button. Those sections are
NAMED RANGES.
The macro consists simply of:
=FORMULA.GOTO(Sheet & Named Range)
=SELECT ("RC3")
The NAMED RANGES are created by going INSERT > NAME > DEFINE , and then
selecting a group of cells that fill the screen.
The SELECT command places you on the first cell that requires data entry in
the section.
Now, when the workbook is first set up all works great. Press the button and
you go to the correct section front and center and you're in the cell to
start work. But a problem arises because it is inherently necessary to add
rows to each new sheet due to the unique conditions found on varying
projects. When rows are added, pushing the original rows down, the relative
position of the named range does not move with it. Excel keeps the range in
the particular area on the worksheet, not on the group of cells selected, so
when you use the button after adding rows the section you are going to is
far down on the screen or off the screen in some instances.
I know there must be a way to do what I want, I'm just not smart enough to
figure it out. I've tried "Locking" the cells using the $ symbol in the
named range but that didn't work. Any help?
4.0.)
I use a large excel workbook to do construction estimating.
The WB consists of a main sheet that contains BUTTONS that were created and
assigned to macros on a master macro sheet in the WB. The buttons take you
to the specific section of another worksheet that contains the cells that
return the total on the row corresponding to the button. Those sections are
NAMED RANGES.
The macro consists simply of:
=FORMULA.GOTO(Sheet & Named Range)
=SELECT ("RC3")
The NAMED RANGES are created by going INSERT > NAME > DEFINE , and then
selecting a group of cells that fill the screen.
The SELECT command places you on the first cell that requires data entry in
the section.
Now, when the workbook is first set up all works great. Press the button and
you go to the correct section front and center and you're in the cell to
start work. But a problem arises because it is inherently necessary to add
rows to each new sheet due to the unique conditions found on varying
projects. When rows are added, pushing the original rows down, the relative
position of the named range does not move with it. Excel keeps the range in
the particular area on the worksheet, not on the group of cells selected, so
when you use the button after adding rows the section you are going to is
far down on the screen or off the screen in some instances.
I know there must be a way to do what I want, I'm just not smart enough to
figure it out. I've tried "Locking" the cells using the $ symbol in the
named range but that didn't work. Any help?