Text relation problem...

TheBigDog

Board Regular
Joined
Aug 5, 2002
Messages
59
here is what I have been TRYING to do..

I have a directory of excel spreadsheet, each of them are named according to their part number. The all have similar field just with different design values in each field.

I am creating a master spreadsheet to list the material used in each design (let say it is found in cell A1 in each spreadsheet file) I want to link it dynamically to the file so that if I change the design, the master spreadsheet will automatically be updated.

For instance, in my MASTER spreadsheet, I have a column that stores the part number of the design (which is ALSO the name of the file) and I want to be able to use that in my formula to pull the content of any cell in that file. For instance, I have a column of part number from 1 to 10.

1
2
3
4
5
6

On the column right next to it, it has
"='C:[1.xls]Sheet1'!A1" which will look into the spreadsheet 1.xls and grab the content of cell A1. Can I relate the name of the spreadsheet (1.xls in this case) to the cell content to the LEFT of it (which is "1") so that I can "automate or populate" the whole column of data by just pulling the lower right hand corner of the cell and drag it down?
This message was edited by TheBigDog on 2002-08-21 15:39
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi,

One way would be to use indiret - see the following:
Book3
ABCD
111:isevaluatingto"sheet1!A1"
222:isevaluatingto"sheet2!A1"
333:isevaluatingto"sheet3!A1"
4
5
Master


Does this help?

Paddy
 
Upvote 0
Hi there The BigDog:

Welcome to the Board!

If I have understood you correctly, you can use the HYPERLINK function to accomplish what you want. See the worksheet simulation.</SPAN>

Regards!

Yogi
This message was edited by Yogi Anand on 2002-08-21 15:33
 
Upvote 0
PaddyD is "kinda" the way that I am looking for. The hyperlink idea is good however I need it to dislapy the value ON THE GO in my master spreadsheet.

PaddyD, I tried your idea but it doesn't seem to work when you call for a sheet that's in a DIFFERENT SPREADSHEET. It gives me a #REF!

My line looks something like this...

=INDIRECT("C:["&A21&".xls]Sheet1'!L20")

Cell A21 has the file name. I want to display the content of L20 in the OTHER spreadsheet (whose filename is the the content of cell A21 in the CURRENT spreadsheet)

Any idea?
This message was edited by TheBigDog on 2002-08-21 16:30
This message was edited by TheBigDog on 2002-08-21 16:31
This message was edited by TheBigDog on 2002-08-21 16:31
This message was edited by TheBigDog on 2002-08-21 16:32
 
Upvote 0
Ooopps - not enough coffee when I replied to your post - should have noticed the external references. A quote from the help file for indirect:

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.

Is it feasible to have the workbooks open?
Paddy

EDIT: If not, take a look at:

http://j-walk.com/ss/excel/tips/tip82.htm

or the 'extract from workbook' page here:

http://www.ozgrid.com/VBA/default.htm

for some further ideas.

Paddy
This message was edited by PaddyD on 2002-08-21 16:56
 
Upvote 0
This master list can have a long list of parts so I don't think I want to be opening each and every single worksheet.

On 2002-08-21 16:45, PaddyD wrote:
Ooopps - not enough coffee when I replied to your post - should have noticed the external references. A quote from the help file for indirect:

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.

Is it feasible to have the workbooks open?
Paddy
 
Upvote 0
Hi there TheBigDog:

I have added to my worksheet simulation a function to just facilitate linking to the cell A1 in Sheet1 of the associated workbook.
y020821.xls
ABCD
11[url='javascript:void(0);'][c:1.xls]Sheet1!A1[/url]=EVAL("'C:["&A1&".xls]sheet1'!A1")
22
33
44
55
66
77
88
99
1010
11
12HYPERLINKINGtotheassociatedfileLinkingtotheassociatedcell
13HithereTheBigDog! Step1IconstructedtheformulaforhyperlinkincellB1 Step2IncellB1thenIusetheformula=HYPERLINK(B1) theremaybeotherwaysofdoingitbutitworksformeLinkingtocellA1inSheet1 EVALisaUDF-- partofthemorefuncadd-inthatcanbedownloadedfrom: http://longre.free.fr/english/index.html
14
15
16
17
18
19
Sheet2 (2)
</SPAN>

Regards!

Yogi
 
Upvote 0

Forum statistics

Threads
1,223,520
Messages
6,172,811
Members
452,481
Latest member
Najwan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top