"liseladele" wrote...
Different workbooks.

If these other workbooks will always be open, then as far as I can tell from
how you've described your workbooks you should be able to use something like

=INDIRECT("'["&NameEnrtyHere&".xls]SheetNameHere'!X99")

where NameEntryHere is the placeholder for the cell address where you enter
Smith, SheetNameHere would be the worksheet name in Smith.xls, and X99 is a
placeholder for the address of the cell you're actually interested in.

If your workbooks aren't always open, you'd be best off downloading and
installing Laurent Longre's MOREFUNC.XLL add-in from

http://xcell05.free.fr/english/

and use it's INDIRECT.EXT function. You'd also need to include the
drive/directory path before the file name.