Referencing other spreadsheets dynamically

[Knickerless Parsons]

Hi There,
Hopefully this is a daft question with a simple solution!

I send out a number of identical spreadsheets to people in different
organisations, each recipient will receive the same sheet containing
no data which they fill up with orders. At this stage everyones sheet
is called "orders.xls". I use the old xls format for compatibility,
some users will not have the new version of office.

When I get the sheets back, I rename them according to who's sent
them, then I reference specific cells from each sheet in a master
sheet which collates all the data.

Currently, the name of the order sheets is "hard coded" in the
formulas of the master sheet.

Is there any way that I can make this more dynamic by specifying the
names of the available order sheets within cells on the master,
building the address of the referenced data on the fly, then
retrieving the data I need from the supplied orders?

I suppose I could tell my users that they must stick with fixed file
names and where they have no orders I could create an empty sheet for
them, but this approach is a little sexier!

Thanks,
Dave.

 

[tsides]

Use the INDIRECT function, and then put the people's names in a cell
(exactly in the same format that you use to name their worksheets).

=BobJingle!B4
will return the same result as
=INDIRECT("BobJingle!B4")
will return the same result as
=INDIRECT(A1&"!B4")
as long as cell A1 contains the value BobJingle

 

[Knickerless Parsons]

Use the INDIRECT function, and then put the people's names in a cell
(exactly in the same format that you use to name their worksheets).

=BobJingle!B4
will return the same result as
=INDIRECT("BobJingle!B4")
will return the same result as
=INDIRECT(A1&"!B4")
as long as cell A1 contains the value BobJingle

Thanks, I'll give it a bash in the morning and report back.

 

[Knickerless Parsons]

Use the INDIRECT function, and then put the people's names in a cell
(exactly in the same format that you use to name their worksheets).

=BobJingle!B4
will return the same result as
=INDIRECT("BobJingle!B4")
will return the same result as
=INDIRECT(A1&"!B4")
as long as cell A1 contains the value BobJingle

Just tried your solution and it works a treat! Thanks much
appreciated.

Dave.

 

[tsides]

Glad I could help.