or even worthwhile?

i have a summary sheet with sheet names representing people's names. so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named john
and martha.

so a formula in the summary sheet named Bob would be something like this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill to
enter his data, and then only have to add a sheet named bill, and copy all
the formulas from bob's sheet and they would just work because of the new
sheet name being bill?
now i do a search and replace for the name.

just wondering



--


Gary

If I understand what you are asking. Rather than have one workbook per
person as now, have one sheet per person in a single workbook?

It is possible but consider, the number of worksheets in one workbook is
limited (Excel 2003) by memory. Also the file size could grow
significantly. The upside is that having all the data in one workbook
should speed up access and make maintenance simpler.

To access a specific named range on sheet named Bob, the formula would be
=Bob!Jan_Tot_Loan

You could use a template.xlt file to create new workbooks with the same
formulas, formats etc., for new people. OR if in the same workbook a new
worksheet created for a new user.

The solution depends on the number of names you are likely to need, the
maintenance issue - for example if you wanted to add a new set of data? in
the same workbook it would be reasonably easy in separate workbooks a bit
more difficult, and also consider the memory - workbook size.

--
Cheers
Nigel



"Gary Keramidas" wrote in message
...
or even worthwhile?

i have a summary sheet with sheet names representing people's names. so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named
john
and martha.

so a formula in the summary sheet named Bob would be something like this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill to
enter his data, and then only have to add a sheet named bill, and copy
all
the formulas from bob's sheet and they would just work because of the new
sheet name being bill?
now i do a search and replace for the name.

just wondering



--


Gary

nope, one workbook per person now and in the future. just looking for a way
to not have to change the formula if a new workbook is added.

so, if i create a workbook, named bill. i go to the summary workbook, and a
sheet, name it bill. copy the formulas from an existing sheet and nothing
would have to be done because all the formula would now reference bill's
workbook.

i use =Bob!Jan_Tot_Loan now, but when i create a workbook named bill, i copy
the formulas and search and replace bob with bill.


that's what i am asking, is it even worth it. i didn't think so, but was
looking for someone with more knowledge than me to give an opinion'


--


Gary


"Nigel" wrote in message
...
If I understand what you are asking. Rather than have one workbook per
person as now, have one sheet per person in a single workbook?

It is possible but consider, the number of worksheets in one workbook is
limited (Excel 2003) by memory. Also the file size could grow
significantly. The upside is that having all the data in one workbook
should speed up access and make maintenance simpler.

To access a specific named range on sheet named Bob, the formula would be
=Bob!Jan_Tot_Loan

You could use a template.xlt file to create new workbooks with the same
formulas, formats etc., for new people. OR if in the same workbook a new
worksheet created for a new user.

The solution depends on the number of names you are likely to need, the
maintenance issue - for example if you wanted to add a new set of data? in
the same workbook it would be reasonably easy in separate workbooks a bit
more difficult, and also consider the memory - workbook size.

--
Cheers
Nigel



"Gary Keramidas" wrote in message
...
or even worthwhile?

i have a summary sheet with sheet names representing people's names. so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named
john
and martha.

so a formula in the summary sheet named Bob would be something like this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill to
enter his data, and then only have to add a sheet named bill, and copy
all
the formulas from bob's sheet and they would just work because of the
new
sheet name being bill?
now i do a search and replace for the name.

just wondering



--


Gary

If it works for you, it sounds ok.

But I wouldn't use formulas. I'd use a macro to open each of the workbooks,
copy the worksheet (or the data) and paste into a new workbook.

Each time I needed a fresh consolidated workbook, I'd rerun the macro.

Ron de Bruin has some sample code at:
http://www.rondebruin.nl/copy3.htm
or
http://www.rondebruin.nl/copy2.htm

Gary Keramidas wrote:

or even worthwhile?

i have a summary sheet with sheet names representing people's names. so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named john
and martha.

so a formula in the summary sheet named Bob would be something like this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill to
enter his data, and then only have to add a sheet named bill, and copy all
the formulas from bob's sheet and they would just work because of the new
sheet name being bill?
now i do a search and replace for the name.

just wondering

--

Gary

--

Dave Peterson

i know it takes a while to refresh all of the links in the summary sheet
over the wan. would the macro populate the sheet any faster?

--


Gary


"Dave Peterson" wrote in message
...
If it works for you, it sounds ok.

But I wouldn't use formulas. I'd use a macro to open each of the
workbooks,
copy the worksheet (or the data) and paste into a new workbook.

Each time I needed a fresh consolidated workbook, I'd rerun the macro.

Ron de Bruin has some sample code at:
http://www.rondebruin.nl/copy3.htm
or
http://www.rondebruin.nl/copy2.htm

Gary Keramidas wrote:

or even worthwhile?

i have a summary sheet with sheet names representing people's names. so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named
john
and martha.

so a formula in the summary sheet named Bob would be something like this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill to
enter his data, and then only have to add a sheet named bill, and copy
all
the formulas from bob's sheet and they would just work because of the
new
sheet name being bill?
now i do a search and replace for the name.

just wondering

--

Gary

--

Dave Peterson

Well i'm glad you found my response useful. Since your response now spells
out what you are trying to do with greater clarity. My recommendation is
use program code to compute the values from each worksheet opened don't
embed formulas. That way you have total control over any sheet rather than
deal with the differences in each one.

PS try using codenames for sheets rather than sheetnames that way you need
not worry about tab names changing. Again I reiterate consider using
templates for new clinet names.

--
Cheers
Nigel



"Gary Keramidas" wrote in message
...
nope, one workbook per person now and in the future. just looking for a
way
to not have to change the formula if a new workbook is added.

so, if i create a workbook, named bill. i go to the summary workbook, and
a
sheet, name it bill. copy the formulas from an existing sheet and nothing
would have to be done because all the formula would now reference bill's
workbook.

i use =Bob!Jan_Tot_Loan now, but when i create a workbook named bill, i
copy
the formulas and search and replace bob with bill.


that's what i am asking, is it even worth it. i didn't think so, but was
looking for someone with more knowledge than me to give an opinion'


--


Gary


"Nigel" wrote in message
...
If I understand what you are asking. Rather than have one workbook per
person as now, have one sheet per person in a single workbook?

It is possible but consider, the number of worksheets in one workbook is
limited (Excel 2003) by memory. Also the file size could grow
significantly. The upside is that having all the data in one workbook
should speed up access and make maintenance simpler.

To access a specific named range on sheet named Bob, the formula would
be
=Bob!Jan_Tot_Loan

You could use a template.xlt file to create new workbooks with the same
formulas, formats etc., for new people. OR if in the same workbook a new
worksheet created for a new user.

The solution depends on the number of names you are likely to need, the
maintenance issue - for example if you wanted to add a new set of data?
in
the same workbook it would be reasonably easy in separate workbooks a
bit
more difficult, and also consider the memory - workbook size.

--
Cheers
Nigel



"Gary Keramidas" wrote in message
...
or even worthwhile?

i have a summary sheet with sheet names representing people's names.
so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named
john
and martha.

so a formula in the summary sheet named Bob would be something like
this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill
to
enter his data, and then only have to add a sheet named bill, and copy
all
the formulas from bob's sheet and they would just work because of the
new
sheet name being bill?
now i do a search and replace for the name.

just wondering



--


Gary

ok, nigel. i may do just that.

would a lot of code to bring in data from 12-15 workbooks using worksheet
open be faster or slower than formulas with links?

--


Gary


"Nigel" wrote in message
...
Well i'm glad you found my response useful. Since your response now
spells
out what you are trying to do with greater clarity. My recommendation is
use program code to compute the values from each worksheet opened don't
embed formulas. That way you have total control over any sheet rather than
deal with the differences in each one.

PS try using codenames for sheets rather than sheetnames that way you need
not worry about tab names changing. Again I reiterate consider using
templates for new clinet names.

--
Cheers
Nigel



"Gary Keramidas" wrote in message
...
nope, one workbook per person now and in the future. just looking for a
way
to not have to change the formula if a new workbook is added.

so, if i create a workbook, named bill. i go to the summary workbook, and
a
sheet, name it bill. copy the formulas from an existing sheet and nothing
would have to be done because all the formula would now reference bill's
workbook.

i use =Bob!Jan_Tot_Loan now, but when i create a workbook named bill, i
copy
the formulas and search and replace bob with bill.


that's what i am asking, is it even worth it. i didn't think so, but was
looking for someone with more knowledge than me to give an opinion'


--


Gary


"Nigel" wrote in message
...
If I understand what you are asking. Rather than have one workbook per
person as now, have one sheet per person in a single workbook?

It is possible but consider, the number of worksheets in one workbook
is
limited (Excel 2003) by memory. Also the file size could grow
significantly. The upside is that having all the data in one workbook
should speed up access and make maintenance simpler.

To access a specific named range on sheet named Bob, the formula would
be
=Bob!Jan_Tot_Loan

You could use a template.xlt file to create new workbooks with the same
formulas, formats etc., for new people. OR if in the same workbook a
new
worksheet created for a new user.

The solution depends on the number of names you are likely to need, the
maintenance issue - for example if you wanted to add a new set of data?
in
the same workbook it would be reasonably easy in separate workbooks a
bit
more difficult, and also consider the memory - workbook size.

--
Cheers
Nigel



"Gary Keramidas" wrote in message
...
or even worthwhile?

i have a summary sheet with sheet names representing people's names.
so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named
john
and martha.

so a formula in the summary sheet named Bob would be something like
this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill
to
enter his data, and then only have to add a sheet named bill, and
copy
all
the formulas from bob's sheet and they would just work because of the
new
sheet name being bill?
now i do a search and replace for the name.

just wondering



--


Gary

I'm sure the answer is: It depends.

And probably on lots of things--speed of pc, number of files, where they're
located.

I do know that if the files are stored on a network drive when I do this, then
it's much slower than if they're stored on my local harddrive.


Gary Keramidas wrote:

i know it takes a while to refresh all of the links in the summary sheet
over the wan. would the macro populate the sheet any faster?

--

Gary

"Dave Peterson" wrote in message
...
If it works for you, it sounds ok.

But I wouldn't use formulas. I'd use a macro to open each of the
workbooks,
copy the worksheet (or the data) and paste into a new workbook.

Each time I needed a fresh consolidated workbook, I'd rerun the macro.

Ron de Bruin has some sample code at:
http://www.rondebruin.nl/copy3.htm
or
http://www.rondebruin.nl/copy2.htm

Gary Keramidas wrote:

or even worthwhile?

i have a summary sheet with sheet names representing people's names. so,
john, martha and so on.

then john and martha have separate workbooks in the same folder, named
john
and martha.

so a formula in the summary sheet named Bob would be something like this:
N:\My Documents\Excel\Bob.xls'!Jan_Tot_Loan

would it be advantageous to have the bob.xls part generated from the
sheetname(Bob)? if so, what would the formula look like?

that way, if bill was hired, i have a blank sheet i can copy for bill to
enter his data, and then only have to add a sheet named bill, and copy
all
the formulas from bob's sheet and they would just work because of the
new
sheet name being bill?
now i do a search and replace for the name.

just wondering

--

Gary

--

Dave Peterson

--

Dave Peterson