Automatic Relinking Function

[Guest]

I am trying to write a formula that will look up a value in a column "Results!H1:H18 on a "Results sheet" from last (previous) month and return all of the information on the Results!H1:H18 for this month and link it to Machine#01", Machine#02, Machine#03 column headers on the current month,s worksheet. Here the story: I need data values from the Results!H1:H18 contained in a folder named "March_04" (which contain 53 workbooks, each workbook containing a Results Sheet "Results!H1:H18" representative of machine numbers 1 through 53) our Network. The previous data set reflected on the worksheet comes the "February_04" folder and now I need to replace the data values it into the Excel "data sheet." which has 53 columns representing each the workbooks within the New Months folder. I want to write a formula that will automatically look at the New Month folder "Results!H1:H18" to see if that Machine number is listed. If it is listed, I want it to return the results listed in the column for that Machine. If it is not listed on the New Month "Results!H1:H18," I would like it to leave the column on the new worksheet blank.

I've tried using the macro recorder to create a re-link to New Month target folder, but, I keep getting errors all over the place. I would like to write a function that may cause a pop-up "LookIn" API call. I gues dialog box allow me to point to the New Month folder and look to the column headers on the worksheet for the Machine Numbers then look in the New Month target folder for the Excel file name "Machine#01", Machine#02, Machine#03 and so forth and forth, then re-link any machine if finds to the calling worksheet.

Is this possible? If it is, can some one write a sample function or code that will do the above mentioned task

Thanks,

 

[steve smallman]

Terri,

If I understand your needs, you have a moving target for
cell references.

In Cell A1 I would enter the UNC folder path one level
above your targets

\\server\data\machines\report or whatever
in cell A2 I would specify this month's target folder
MARCH04

in the data area, I would enter a string formula like
=indirect(a1 & "\" & a2 &"!h1")

I haven't tested this but it may be of some assistance

Steve

-----Original Message-----
I am trying to write a formula that will look up a value

in a column "Results!H1:H18 on a "Results sheet" from
last (previous) month and return all of the information
on the Results!H1:H18 for this month and link it to
Machine#01", Machine#02, Machine#03 column headers on the
current month,s worksheet. Here the story: I need data
values from the Results!H1:H18 contained in a folder
named "March_04" (which contain 53 workbooks, each
workbook containing a Results Sheet "Results!H1:H18"
representative of machine numbers 1 through 53) our
Network. The previous data set reflected on the worksheet
comes the "February_04" folder and now I need to replace
the data values it into the Excel "data sheet." which
has 53 columns representing each the workbooks within the
New Months folder. I want to write a formula that will
automatically look at the New Month folder "Results!
H1:H18" to see if that Machine number is listed. If it
is listed, I want it to return the results listed in the
column for that Machine. If it is not listed on the New
Month "Results!H1:H18," I would like it to leave the
column on the new worksheet blank.

I've tried using the macro recorder to create a re-link

to New Month target folder, but, I keep getting errors
all over the place. I would like to write a function that
may cause a pop-up "LookIn" API call. I gues dialog box
allow me to point to the New Month folder and look to the
column headers on the worksheet for the Machine Numbers
then look in the New Month target folder for the Excel
file name "Machine#01", Machine#02, Machine#03 and so
forth and forth, then re-link any machine if finds to the
calling worksheet.

Is this possible? If it is, can some one write a sample

function or code that will do the above mentioned task.