Variables in Cell References

[AnitaMarch]

Hello All,
I would like to use a variable in the following reference,
using a text extract from an existing cell to refer to a
tab (as follows). So far it has eluded me! Can anyone
help.
List entry for example:
6930 123 (one cell)

Formula:
=+'[workbook.xls]6930'!$F$10

I would like to pull the tab name (i.e. 6930)from the list
entry, instead of having to manually input.

All ideas welcome!
Anita

 

[Harlan Grove]

I would like to use a variable in the following reference,
using a text extract from an existing cell to refer to a
tab (as follows). So far it has eluded me! Can anyone
help.

First, by 'tab' I assume you mean worksheet.

List entry for example:
6930 123 (one cell)

So the worksheet name would always come first and would always be a string of
non-space characters? If so, the worksheet name may be extracted using

=LEFT(OneCell,FIND(" ",OneCell)-1)

Formula:
=+'[workbook.xls]6930'!$F$10

...

Looks like the workbook.xls file would be open. If so, you could use

=INDIRECT("'[workbook.xls]"&LEFT(OneCell,FIND(" ",OneCell)-1)&"'!F10")

If workbook.xls could be closed, you'll need to use VBA to values data from it.

 

[AnitaMarch]

Works like a charm.....You are a prince!
Thx.
Anita

-----Original Message-----

I would like to use a variable in the following reference,
using a text extract from an existing cell to refer to a
tab (as follows). So far it has eluded me! Can anyone
help.

First, by 'tab' I assume you mean worksheet.

List entry for example:
6930 123 (one cell)

So the worksheet name would always come first and would always be a string of
non-space characters? If so, the worksheet name may be extracted using

=LEFT(OneCell,FIND(" ",OneCell)-1)

Formula:
=+'[workbook.xls]6930'!$F$10

...

Looks like the workbook.xls file would be open. If so, you could use

=INDIRECT("'[workbook.xls]"&LEFT(OneCell,FIND (" ",OneCell)-1)&"'!F10")

If workbook.xls could be closed, you'll need to use VBA to values data from it.