Hi all,

I will be posting many questions as I just got a temp job from friend
automating a complex Excel report, that is now done manually, and that
relies on several other Excel reports to populate it's data. Each
report is a seperate Workbook.

I know very little about Excel, beyond the absolute basics. I got the
job as a favor, and because my friend has confidence that I can figure
it out (which I am doing on my own time).

My first problem is this. One requirement is that when the main report
is run that it grabs the date automatically from each sub-report and
inserts into the proper section. The date field is only one cell in
each report, and only needs to populate one cell in the correct section
on the final report.

I have tried variations on MATCH, LOOKUP, VLOOKUP, HLOOKUP and even
tried the LOOKUP wizard addin (which was no help). I always get the
same result... #N/A. I have looked at all the Excel help files that
seemed relevent, and combed the web (including here) trying to find the
answer. Part of the problem is my complete lack of experience with
Excel, and because of that, I'm not sure that I understand some of what
I've read. I may have found the answer and not understood it.

I have had no problem (so far) using VLOOKUP to grab the column values
I need from the various reports. I don't understand why I can't, or how
I go about grabing a single value from a single cell!?


---
Message posted from http://www.ExcelForum.com/

to get cell A1 found in Sheet1 of a workbook named Report5.xls

=[Report5.xls]Sheet1!$A$1

if the workbook is open

=c:\MyReports\[Report5.xls]Sheet1!$A$1

if the workbook report5.xls is not open


if worksheets are in the same workbook

=Sheet1!$A$1

if in the same sheet

=$A$1


==
Regards,
Tom Ogilvy

ExcelNovice wrote in message
...
Hi all,

I will be posting many questions as I just got a temp job from friend
automating a complex Excel report, that is now done manually, and that
relies on several other Excel reports to populate it's data. Each
report is a seperate Workbook.

I know very little about Excel, beyond the absolute basics. I got the
job as a favor, and because my friend has confidence that I can figure
it out (which I am doing on my own time).

My first problem is this. One requirement is that when the main report
is run that it grabs the date automatically from each sub-report and
inserts into the proper section. The date field is only one cell in
each report, and only needs to populate one cell in the correct section
on the final report.

I have tried variations on MATCH, LOOKUP, VLOOKUP, HLOOKUP and even
tried the LOOKUP wizard addin (which was no help). I always get the
same result... #N/A. I have looked at all the Excel help files that
seemed relevent, and combed the web (including here) trying to find the
answer. Part of the problem is my complete lack of experience with
Excel, and because of that, I'm not sure that I understand some of what
I've read. I may have found the answer and not understood it.

I have had no problem (so far) using VLOOKUP to grab the column values
I need from the various reports. I don't understand why I can't, or how
I go about grabing a single value from a single cell!?


---
Message posted from http://www.ExcelForum.com/

Thanks Tom,

That was almost the answer, but close enough that I figured it out. You
only neglected to include single quotes around the file name and path.
Thanks again for the help... as the govenor of California would say,
"I'll be back" ;^)


---
Message posted from http://www.ExcelForum.com/

For anyone else as clueless as myself having a similar problem, th
single qoutes should also include the sheet name thus:

='C:\reports\[report5.xls]Sheet1'!$C$1

Cheers, and thanks again for the fast help

--
Message posted from http://www.ExcelForum.com

You don't need single quotes in the example I gave - only if the the string
has spaces in it. But I should have mentioned it.

--
Regards,
Tom Ogilvy

ExcelNovice wrote in message
...
Thanks Tom,

That was almost the answer, but close enough that I figured it out. You
only neglected to include single quotes around the file name and path.
Thanks again for the help... as the govenor of California would say,
"I'll be back" ;^)


---
Message posted from http://www.ExcelForum.com/

I just tried again and Excel pops up an error window when I try it
without the quotes, and the file and path have no spaces. I am using
Excel 2000 here, but Excel 2003 at the job. I'll test it in 2003
tomorrow just to see.

All that matters to me though is that you helped me fix the problem.
Thanks again!


---
Message posted from http://www.ExcelForum.com/

I was just playing around and found this to be true. When the cell is i
another sheet in the same workbook, it does not need the quotes i
there are no spaces in the sheet name, and does if there are spaces. A
least in Excel 2000

--
Message posted from http://www.ExcelForum.com

You are correct that if in another workbook, it needs the single quotes - my
mistake.

--
Regards,
Tom Ogilvy


ExcelNovice wrote in message
...
I was just playing around and found this to be true. When the cell is in
another sheet in the same workbook, it does not need the quotes if
there are no spaces in the sheet name, and does if there are spaces. At
least in Excel 2000.


---
Message posted from http://www.ExcelForum.com/

No Problem... like I said, I couldn't figure it out at all before yo
helped. And the solution you gave makes sense now that I know it. M
friend who hired me for this knows I'm usually good a figuring thes
things out. That's why he didn't care that I don't know Excel

--
Message posted from http://www.ExcelForum.com

It was driving me nuts that I figured out VLOOKUP for the column values
fairly quikly and easily, but couldn't figure out how to grab a stupid
single cell value. Thanks to you, I'll still look smart tomorrow ;^)


---
Message posted from http://www.ExcelForum.com/

Excel was not on my list of applications I wanted learn... but these
days you have to take what ever work you can.

I'll probably have some more difficult questions later, but I will try
and figure them out myself first.


---
Message posted from http://www.ExcelForum.com/