Concatenate and linked spreadsheets 1

[PennyB]

I'm trying to concatenate two cells from a linked spreadsheet in excel 2002, but the formula

=CONCATENATE([spreadsheet1.xls]Tab'!$I$112," ",[spreadsheet1.xls]Tab'!$J$112)

says "That name is not valid".

Hope youcan help me Thanks in advance

Penny

 

[xlbo]

Hi PeenyB - As a pointer for future ref, you really don't need to use the Concatenate formula - far too much typing - you can simply use the ampersand (&) instead so:

=[spreadsheet1.xls]Tab'!$I$112 & " " & [spreadsheet1.xls]Tab'!$J$112

would produce the same results

As to the issue with the formula....is the spreadsheet name really "spreadsheet1.xls" and is the worksheet really called "Tab" ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PennyB]

Hi Xlbo

The actual formula is:

=CONCATENATE([Ad Ed Bookings Sept05.xls]TUES'!$I$112," ",[Ad Ed Bookings Sept05.xls]TUES'!$J$112)


Penny

 

[xlbo]

howzabout this:

=CONCATENATE('[Ad Ed Bookings Sept05.xls]TUES'!$I$112," ",'[Ad Ed Bookings Sept05.xls]TUES'!$J$112)

which could then be transformed into:
='[Ad Ed Bookings Sept05.xls]TUES'!$I$112 & " " & '[Ad Ed Bookings Sept05.xls]TUES'!$J$112


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PennyB]

Thanks Xlbo, it worked

 

[KenWright]

Geoff has given you the answer, but just in case you are wondering why, I'm betting that you knocked up a quick dummy formula based on a test spreadsheet, and then edited to get what you wanted on your real one.

What has happened is that your test spreadhseet has no spaces in either the filename or the tab name, and as such no quotes are required in the reference. In your real spreadsheet however, you have spaces in the filename, and as such require single quotes to show Excel where the names begin and end.

Because you introduced spaces to a name that was not surrounded by quotes, Excel now has no clue as to what you are trying to do, and so errors out on you. As soon as you fix that by using quotes, as Geoff has shown you, all should work fine.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------