Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- cradino ------------------------------------------------------------------------ cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521 View this thread: http://www.excelforum.com/showthread...hreadid=375741 |
#2
|
|||
|
|||
=INDIRECT(B1&"!A1") Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=375741 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 -- cradino ------------------------------------------------------------------------ cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521 View this thread: http://www.excelforum.com/showthread...hreadid=375741 |
#5
|
|||
|
|||
Lowkey Thanks to you too. -- cradino ------------------------------------------------------------------------ cradino's Profile: http://www.excelforum.com/member.php...o&userid=18521 View this thread: http://www.excelforum.com/showthread...hreadid=375741 |
#6
|
|||
|
|||
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 PasteSpecialTranspose to the horizontal array of cells that you want to contain the list. Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=375741 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel |