Reference to another worksheet

[cradino]

Assuming I have
Sheet1!A1 with value 100
Sheet2!B1 with "Sheet1" without cotes - the Sheet1 name

In
Sheet2!B2 I want the same value of Sheet1!A1

Firs way
=Sheet1!A1 and the result is 100

But I want to refer Sheet1, in that formula, by its name typed in
Sheet2!B1
Then I tried fill in Sheet2!B2 this
=Indirect(B1)!A1 but it gives error.

HOW CAN I REFER SHEETS BY ITS NAMES TYPED IN ONE PARTICULAR SHEET??

GRATIAS FOR HELP

 

[Guest]

Cradino,

I don't think INDIRECT will serve your purpose if you always want Sheet2!B2
I want the same value of Sheet1!A1 as you say below. The INDIRECT function
will always refers to the contents of Sheet1!A1 regardless of the changes you
make to Sheet1.

The easiest solution is to select B1 on Sheet2, press = and then go to Sheet
1, select A1 and hit enter. You could also cut and then paste special and
choose from the options given, most likely "paste formula".

Other than that, it sounds like you're describing some kind of code to the
result of the cell in A1, like George. So that if you type in George in B1 it
will result in the formula you named for Sheet1!A1. I don't know if that's
possible.

Otherwise, you'll have to be more clear.

 

[cradino]

Ron Coderre

That's great.
But now I want to paste my workbooksheet names in the first row of th
first sheet and that formula in the seond row.

Like this: sheet1!A1:sheet1A200 SheetNames
Sheet2!A1 =INDIRECT(A1&"!A1") Right Filled til
Sheet1!A200 That formula is great!!!!!!!!!!!

And now the quastion: How can I automaticaly fil
sheet1!A1:sheet1!A200 with MySheetNames????????????????????

Best regard

 

[cradino]

Lowkey
Thanks to you too

 

[Ron Coderre]

Ok...you're gonna love this: DOS!!

Yes, you could write a program, but this is so easy:

1)You need to know the complete path to your Excel files
In my case: C:\ANALYSIS\

2)Open a new text file and enter this text:
dir/b c:\analysis\*.xls >> c:\mylist.txt

Note: you'll need to replace my locations with yours

3)Save that file to your desktop as ListXLFiles.BAT, then close it.

4)Display your desktop and double click that file.

5)In Excel, open c:\mylist.txt
In the file import wizard, uncheck all delimiters and click [Finish]

There's your list of filesl
Now you can copy that list and Paste>Special>Transpose to the
horizontal array of cells that you want to contain the list.

Does that help?

Ron