MrExcel Publishing
Your One Stop for Excel Tips & Solutions

referencing data in different workbook


Posted by itgirl168 on September 24, 2001 11:10 AM

Hi
I have two workbooks. I would like a cell in workbook A to equal the sum of a range of cells found in workbook B. I have tried hightlighting the cell in workbook A and typing in '=SUM' and then highlighting the cells that I want to add up on workbook B. The formula comes up as:
=SUM+'[TicketAging.xls]Ticket Aging Information'!$I$15:$I$42
However, when I hit enter, I get the error message: The text you enetered is not a valid reference or defined name.

What am I missing here?

Any help would be much appreciated!


Posted by Aladin Akyurek on September 24, 2001 11:15 AM

Change

=SUM+'[TicketAging.xls]Ticket Aging Information'!$I$15:$I$42

to

=SUM('[TicketAging.xls]Ticket Aging Information'!$I$15:$I$42)

Aladin

Posted by itgirl168 on September 24, 2001 11:27 AM

HI Aladin,
It still gives me the same error message and highlights the word SUM. Any other ideas? I really appreciate your help

Posted by itgirl168 on September 24, 2001 11:28 AM

HI Aladin,
It still gives me the same error message and highlights the word SUM. Any other ideas? I really appreciate your help

Posted by Aladin Akyurek on September 24, 2001 11:34 AM

The formula says: sum the range I15:I42 in Ticket Aging Information Sheet in workbook TicketAging.xls, so it should work. Is the target workbook open when you try out the formula?

Posted by Juan Pablo on September 24, 2001 11:56 AM

Are you working with an nonEnglish version of Excel ?

If you're working in Spanish, for example, the formula would be

=SUMA('[TicketAging.xls]Ticket Aging Information'!$I$15:$I$42)

Juan Pablo

------------------

Posted by itgirl168 on September 24, 2001 12:09 PM

no, it's not non-english.....

Posted by itgirl168 on September 24, 2001 12:10 PM


yes, the other workbook is open when I'm trying the formula....

Posted by Aladin Akyurek on September 24, 2001 12:32 PM

Would you try the following:

Go to the workbook TicketAging.xls, select the range of interest, go the Name Box and type SDATA.

Go to the workbook where you want to do summing.

Enter in the target cell:

=SUM(TicketAging!SDATA)

Aladin

PS. If this doesn't work, I'd like to see that TicketAging.xls.

===========

Posted by itgirl168 on September 24, 2001 1:16 PM

Posted by itgirl168 on September 24, 2001 1:20 PM

I think the problem is that I am using a text box instead of a cell. I have other text boxes that reference the value of a cell in a different worksheet so I thought it could reference the sum of a range of cells. Am I wrong?

If I use a regular cell, the formula works fine.

thanks,

Judy

Posted by Aladin Akyurek on September 24, 2001 1:24 PM

Judy: In that case, you need a VBA programmer. (NT)

Posted by itgirl168 on September 24, 2001 1:45 PM

Re: Judy: In that case, you need a VBA programmer. (NT)

Okay, thanks Aladin I think the problem is that I am using a text box instead of a cell. I have other text boxes that reference the value of a cell in a different worksheet so I thought it could reference the sum of a range of cells. Am I wrong? If I use a regular cell, the formula works fine. thanks, Judy