formatting help

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

I have 2 excel files linked. I have a first & last name in the same cell in
one workbook and I want to link the cellin the second workbook, but I only
want the last name in the result. Any way I can do this. I figured out how
to get the last name but not how to word it in the formula
=RIGHT(A5,LEN(A5)-FIND(" ",A5)) . Any help will be greatly appreciated.
 
Mac,

If the formula you create below gets you the last name like you desired,
then it seems like you answered your own question. Looking at the formula, it
says the text you are looking at is A5. If you were really looking into
another workbook for that text, it would say something more like
='[Workbook.xls]Sheet1'!$A$5. That is if your linking to an Excel document
called Workbook and the data is on Sheet1.
 
Thank you for replying, the problem is I can get the data but I am getting
the first & last name, I only want the last name. That formula will work
within the workbook but not outside of it.

curious engineer said:
Mac,

If the formula you create below gets you the last name like you desired,
then it seems like you answered your own question. Looking at the formula, it
says the text you are looking at is A5. If you were really looking into
another workbook for that text, it would say something more like
='[Workbook.xls]Sheet1'!$A$5. That is if your linking to an Excel document
called Workbook and the data is on Sheet1.

Mac said:
I have 2 excel files linked. I have a first & last name in the same cell in
one workbook and I want to link the cellin the second workbook, but I only
want the last name in the result. Any way I can do this. I figured out how
to get the last name but not how to word it in the formula
=RIGHT(A5,LEN(A5)-FIND(" ",A5)) . Any help will be greatly appreciated.
Click to expand...
Click to expand...
 
If A5 is in a different sheet in a different workbook, then you will
need to change each reference to A5 to something like
'[Workbook.xls]Sheet1'!A5 as previously advised. Your formula would
then become:

=RIGHT([Workbook.xls]Sheet1'!A5,LEN([Workbook.xls]Sheet1'!A5)-FIND(" ",
[Workbook.xls]Sheet1'!A5))

although if Workbook.xls is not open at the same time, then you would
also have to include the full path to the file as well.

Hope this helps.

Pete

Thank you for replying,  the problem is I can get the data but I am getting
the first & last name,  I only want the last name. That formula will work
within the workbook but not outside of it.



curious engineer said:
Mac,
Click to expand...
If the formula you create below gets you the last name like you desired,
then it seems like you answered your own question. Looking at the formula, it
says the text you are looking at is A5. If you were really looking into
another workbook for that text, it would say something more like
='[Workbook.xls]Sheet1'!$A$5. That is if your linking to an Excel document
called Workbook and the data is on Sheet1.
Click to expand...
"Mac" wrote:
Click to expand...

- Show quoted text -
Click to expand...
 
Hi Pete,

Thank you for your help. I tried the formula and I still get "value" I am
posting my formula hoping that you can help. It is driving me crazy. Again
thank y ou .

=RIGHT('[2007 Client.xls]Highest Paid Employee'!$C$16,LEN('[2007
Client.xls]Highest Paid Employee'!$C$16-FIND(" ",'[2007 Client.xls]Highest
Paid Employee'!$C$16)))

Pete_UK said:
If A5 is in a different sheet in a different workbook, then you will
need to change each reference to A5 to something like
'[Workbook.xls]Sheet1'!A5 as previously advised. Your formula would
then become:

=RIGHT([Workbook.xls]Sheet1'!A5,LEN([Workbook.xls]Sheet1'!A5)-FIND(" ",
[Workbook.xls]Sheet1'!A5))

although if Workbook.xls is not open at the same time, then you would
also have to include the full path to the file as well.

Hope this helps.

Pete

Thank you for replying, the problem is I can get the data but I am getting
the first & last name, I only want the last name. That formula will work
within the workbook but not outside of it.



curious engineer said:
Mac,
Click to expand...
If the formula you create below gets you the last name like you desired,
then it seems like you answered your own question. Looking at the formula, it
says the text you are looking at is A5. If you were really looking into
another workbook for that text, it would say something more like
='[Workbook.xls]Sheet1'!$A$5. That is if your linking to an Excel document
called Workbook and the data is on Sheet1.
Click to expand...
"Mac" wrote:
Click to expand...
I have 2 excel files linked. I have a first & last name in the same cell in
one workbook and I want to link the cellin the second workbook, but I only
want the last name in the result. Any way I can do this. I figured out how
to get the last name but not how to word it in the formula
=RIGHT(A5,LEN(A5)-FIND(" ",A5)) . Any help will be greatly appreciated. - Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
You need to have a closing bracket after the second C16, so that it is
LEN( ... ), and to remove one of the closing brakets at the end. So,
your formula would become:

=RIGHT('[2007 Client.xls]Highest Paid Employee'!$C$16,LEN('[2007
Client.xls]Highest Paid Employee'!$C$16)-FIND(" ",'[2007
Client.xls]Highest Paid Employee'!$C$16))

You might like to <copy> it from here and paste directly into your
sheet, using CTRL-C and CTRL-V, to avoid typing mistakes.

Hope this helps.

Pete

Hi Pete,

Thank you for your help.  I tried the formula and I still get "value"  I am
posting my formula hoping that you can help.  It is driving me crazy.  Again
thank y ou .

=RIGHT('[2007 Client.xls]Highest Paid Employee'!$C$16,LEN('[2007
Client.xls]Highest Paid Employee'!$C$16-FIND(" ",'[2007 Client.xls]Highest
Paid Employee'!$C$16)))



Pete_UK said:
If A5 is in a different sheet in a different workbook, then you will
need to change each reference to A5 to something like
'[Workbook.xls]Sheet1'!A5 as previously advised. Your formula would
then become:
Click to expand...
=RIGHT([Workbook.xls]Sheet1'!A5,LEN([Workbook.xls]Sheet1'!A5)-FIND(" ",
[Workbook.xls]Sheet1'!A5))
Click to expand...
although if Workbook.xls is not open at the same time, then you would
also have to include the full path to the file as well.
Click to expand...
Hope this helps.
Pete
Click to expand...

Thank you for replying,  the problem is I can get the data but I am getting
the first & last name,  I only want the last name. That formula willwork
within the workbook but not outside of it.
:
Mac,
If the formula you create below gets you the last name like you desired,
then it seems like you answered your own question. Looking at the formula, it
says the text you are looking at is A5. If you were really looking into
another workbook for that text, it would say something more like
='[Workbook.xls]Sheet1'!$A$5. That is if your linking to an Excel document
called Workbook and the data is on Sheet1.
:
I have 2 excel files linked.  I have a first & last name in the same cell in
one workbook and I want to link the cellin the second workbook,  but I only
want the last name in the result.  Any way I can do this.  I figured out how
to get the last name but not how to word it in the formula
=RIGHT(A5,LEN(A5)-FIND(" ",A5)) . Any help will be greatly appreciated.  - Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top