Simplify Linking to several worksheets??
 

I have several workbooks that each has 65-70 worksheets in them and I have to
update and balance them each year at the end of the year. The balancing part
has always been a serious chore and I am trying to find a way to simplify it.
I am currently trying to balance from last year before I have to start this
years!!

Currently, I have a report I pull that tells me the total I need to balance
back to, and I keep it in Excel also. I have been linking the cells from
each sheet back to this report sheet but wanted to see if there is a formula
or function I can use that will pull the figure from one cell in each
spreadsheet to the report sheet where I can compare and determine which
sheets do not match as my totals are not matching.

I entered a simple linked formula, but to enter that formula into each cell
will take entirely too long. Copying does not work, it simply pulls the
first sheet total, all the way down the report sheet. Changing the formula
for each line is also very time consuming. I tried a true/false formula, but
with the same problem. The first one works fine, but the copies reference
the first sheet and not the following sheets.

Is there anything I can do to speed this up and make it easier to find where
I am out of balance?


--
G

custom forms created
bookkeeping services
budget consulting

for quotes

Hi
do you have a list of all sheets or do they have a naming convention.
If yes you may use INDIRECT. Just post some more details about:
- how the sheets are all named
- which cells you want to reference

--
Regards
Frank Kabel
Frankfurt, Germany

"Geneva" schrieb im Newsbeitrag
...
I have several workbooks that each has 65-70 worksheets in them and I
have to
update and balance them each year at the end of the year. The
balancing part
has always been a serious chore and I am trying to find a way to
simplify it.
I am currently trying to balance from last year before I have to
start this
years!!

Currently, I have a report I pull that tells me the total I need to
balance
back to, and I keep it in Excel also. I have been linking the cells
from
each sheet back to this report sheet but wanted to see if there is a
formula
or function I can use that will pull the figure from one cell in each
spreadsheet to the report sheet where I can compare and determine
which
sheets do not match as my totals are not matching.

I entered a simple linked formula, but to enter that formula into
each cell
will take entirely too long. Copying does not work, it simply pulls
the
first sheet total, all the way down the report sheet. Changing the
formula
for each line is also very time consuming. I tried a true/false
formula, but
with the same problem. The first one works fine, but the copies
reference
the first sheet and not the following sheets.

Is there anything I can do to speed this up and make it easier to
find where
I am out of balance?


--
G

custom forms created
bookkeeping services
budget consulting

for quotes

Hmmm, not used INDIRECT before. I'll look into that!

The sheets are named by clock number (these are employee records) - they are
numeric, but skip around due to terminations/retirements, etc.

The cell I need to reference is the same cell in each, so making an absolute
reference is the way I have the formula written.

for example: My first formula goes like this(file location on the
lan)[Pension files 0005-0519.xls]0005'!$H$61

Geneva


"Frank Kabel" wrote:

Hi
do you have a list of all sheets or do they have a naming convention.
If yes you may use INDIRECT. Just post some more details about:
- how the sheets are all named
- which cells you want to reference

--
Regards
Frank Kabel
Frankfurt, Germany

"Geneva" schrieb im Newsbeitrag
...
I have several workbooks that each has 65-70 worksheets in them and I
have to
update and balance them each year at the end of the year. The
balancing part
has always been a serious chore and I am trying to find a way to
simplify it.
I am currently trying to balance from last year before I have to
start this
years!!

Currently, I have a report I pull that tells me the total I need to
balance
back to, and I keep it in Excel also. I have been linking the cells
from
each sheet back to this report sheet but wanted to see if there is a
formula
or function I can use that will pull the figure from one cell in each
spreadsheet to the report sheet where I can compare and determine
which
sheets do not match as my totals are not matching.

I entered a simple linked formula, but to enter that formula into
each cell
will take entirely too long. Copying does not work, it simply pulls
the
first sheet total, all the way down the report sheet. Changing the
formula
for each line is also very time consuming. I tried a true/false
formula, but
with the same problem. The first one works fine, but the copies
reference
the first sheet and not the following sheets.

Is there anything I can do to speed this up and make it easier to
find where
I am out of balance?


--
G

custom forms created
bookkeeping services
budget consulting

for quotes

Hi
I thought your data is within one SINGLE file?

--
Regards
Frank Kabel
Frankfurt, Germany

"Geneva" schrieb im Newsbeitrag
...
Hmmm, not used INDIRECT before. I'll look into that!

The sheets are named by clock number (these are employee records) -
they are
numeric, but skip around due to terminations/retirements, etc.

The cell I need to reference is the same cell in each, so making an
absolute
reference is the way I have the formula written.

for example: My first formula goes like this(file location on the
lan)[Pension files 0005-0519.xls]0005'!$H$61

Geneva


"Frank Kabel" wrote:

Hi
do you have a list of all sheets or do they have a naming
convention.
If yes you may use INDIRECT. Just post some more details about:
- how the sheets are all named
- which cells you want to reference

--
Regards
Frank Kabel
Frankfurt, Germany

"Geneva" schrieb im Newsbeitrag
...
I have several workbooks that each has 65-70 worksheets in them
and I
have to
update and balance them each year at the end of the year. The
balancing part
has always been a serious chore and I am trying to find a way to
simplify it.
I am currently trying to balance from last year before I have to
start this
years!!

Currently, I have a report I pull that tells me the total I need
to
balance
back to, and I keep it in Excel also. I have been linking the
cells
from
each sheet back to this report sheet but wanted to see if there
is a
formula
or function I can use that will pull the figure from one cell in
each
spreadsheet to the report sheet where I can compare and determine
which
sheets do not match as my totals are not matching.

I entered a simple linked formula, but to enter that formula into
each cell
will take entirely too long. Copying does not work, it simply
pulls
the
first sheet total, all the way down the report sheet. Changing
the
formula
for each line is also very time consuming. I tried a true/false
formula, but
with the same problem. The first one works fine, but the copies
reference
the first sheet and not the following sheets.

Is there anything I can do to speed this up and make it easier to
find where
I am out of balance?


--
G

custom forms created
bookkeeping services
budget consulting

for quotes

No, sorry, it is in several workbooks.

"Frank Kabel" wrote:

Hi
I thought your data is within one SINGLE file?

--
Regards
Frank Kabel
Frankfurt, Germany

"Geneva" schrieb im Newsbeitrag
...
Hmmm, not used INDIRECT before. I'll look into that!

The sheets are named by clock number (these are employee records) -
they are
numeric, but skip around due to terminations/retirements, etc.

The cell I need to reference is the same cell in each, so making an
absolute
reference is the way I have the formula written.

for example: My first formula goes like this(file location on the
lan)[Pension files 0005-0519.xls]0005'!$H$61

Geneva


"Frank Kabel" wrote:

Hi
do you have a list of all sheets or do they have a naming
convention.
If yes you may use INDIRECT. Just post some more details about:
- how the sheets are all named
- which cells you want to reference

--
Regards
Frank Kabel
Frankfurt, Germany

"Geneva" schrieb im Newsbeitrag
...
I have several workbooks that each has 65-70 worksheets in them
and I
have to
update and balance them each year at the end of the year. The
balancing part
has always been a serious chore and I am trying to find a way to
simplify it.
I am currently trying to balance from last year before I have to
start this
years!!

Currently, I have a report I pull that tells me the total I need
to
balance
back to, and I keep it in Excel also. I have been linking the
cells
from
each sheet back to this report sheet but wanted to see if there
is a
formula
or function I can use that will pull the figure from one cell in
each
spreadsheet to the report sheet where I can compare and determine
which
sheets do not match as my totals are not matching.

I entered a simple linked formula, but to enter that formula into
each cell
will take entirely too long. Copying does not work, it simply
pulls
the
first sheet total, all the way down the report sheet. Changing
the
formula
for each line is also very time consuming. I tried a true/false
formula, but
with the same problem. The first one works fine, but the copies
reference
the first sheet and not the following sheets.

Is there anything I can do to speed this up and make it easier to
find where
I am out of balance?


--
G

custom forms created
bookkeeping services
budget consulting

for quotes