Reference to another worksheet
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
=INDIRECT(B1&"!A1")
Does that help?
Ron
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
List sheets of a workbook
Ron Coderre
That's great.
But now I want to paste my workbooksheet names in the first row of the first sheet and that formula in the seond row.
Like this: sheet1!A1:sheet1A200 SheetNames
Sheet2!A1 =INDIRECT(A1&"!A1") Right Filled till Sheet1!A200 That formula is great!!!!!!!!!!!
And now the quastion: How can I automaticaly fill sheet1!A1:sheet1!A200 with MySheetNames????????????????????
Best regards
List sheets of a workbook
Lowkey
Thanks to you too.
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
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
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" wrote:
>
> 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
>
>
> --
> cradino
> ------------------------------------------------------------------------
> cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521
> View this thread: http://www.excelforum.com/showthread...hreadid=375741
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks