Excel question: referencing different worksheet?

Question

I have a question regarding referencing. I am trying to reference to different worksheets. Currently, I have daily worksheets and their names are basically in mmddyy format, i.e. 071606 for July 16, 2006. I need to lookup (or reference) to these worksheet, but I don't want to look them up individually - i.e., clicking to the worksheet and to the cell one by one. Is there a easy way to do this? I can list all dates, convert them to text so it matches the individual worksheet names, but I cannot use this text string in referencing. Thanks!

Perhaps I need to explain my situation better;

1. I have worksheets named "071506", "071606", and so forth. As you can see, their names are actually dates, i.e 071506 is July 15, 2006.
2. I have a summary worksheet which contains 2 columns; 1 for dates and 1 for daily balance. I need to pull daily balance information from the individual worksheets explained in #1.
3. I am looking for a solution that when the "date" changes, the vlookup will change the refererence of the worksheet automatically. So instead of copying and pasting each day, I can just use the formula.

Hope this is clearer. Thank you.

My email address is [e-mail]

Answer 1

You can do this but it has a wrinkle. But first, you can build a formula dynamicaly that uses the contents of a cell. What that means is you could type in a date in a cell and have another cell that uses the date in a dynamic formula.

Here's an example of one I did:

=INDIRECT (ADDRESS(9, 1, 1, FALSE,"C:\Documents and Settings\Ken\My Documents\[" & C1 & "]Sheet1"), FALSE)

In cell C1 is the file name. The path to the file is the string begining with "C:\Documents..... until next double quote. The next string that shows "]Sheet1" is the first sheet in the file, you can change that to whatever sheet number in your case.

I'll leave it up to you to read help on INDIRECT and ADDRESS functions because I think you can handle it.

Now for the wrinkle. The INDIRECT function has this remark in the help: "If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value. "

So there you have it. This works, you CAN reference another workbook using this method. Unfortunately, the workkbook must be open. I don't know another way around the problem.

Update: I read your additional details and it doesn't change my answer because that's what I thought you were doing. My explanation allows you to change the filename in a cell that is used to lookup in another worksheet. Only problem is the source file must be opened.

That is still a lot less work than copy/paste each day.

Anyway, maybe you should think outside the structure of your question. I assume you're receiving the daily files from a source other than yourself. Right?

If that is true, then you need better control of the input, not try to customize the way you read their inputs.

Please read my previous answer to what I think is a similar situation. Here's the link: http://answers.yahoo.com/question/index;_ylt=Anu2Po4mcee_9SHMSvZEqozsy6IX?qid=20060703091645AAaUQUq


What I think you need is described in that answer. You can control inputs and submit them to a database sheet. Then your lookup tool can reference the same database sheet always.

You see, the filename (date) in your situation is just a partial key to the data/record you want. Simply include that date in your input control that submits to your database sheet.

By the way, I recommend date format "YYYYMMDD". In time, you will understand why.

Regards,
QwertyKPH

Answer 2

What is exactly what you need?
- Do you need a particular cell in each sheet?
you can use this function in D2 for calling A1 from each sheet named in C2:
=Indirect(address(1,1,,,C2))
- Do you want to search for a value in each sheet?
that is another issue.

Please, send me your address, and more info through Yahoo! Answers and I can do it for you

Read my profile, I am XLMan

Answer 3

No this is the way to do it, you could always type wht you wanted

Answer 4

You welcome

http://experts.about.com/q/Microsoft-Word-1058/Mail-Merge-Excel-using.htm