How to change link for certain cells and not for the entire workbook

[Toetegaai]

Hi guys,

I have a question which I'am unable to answer.

I have a workbook in which certain cells (range C15:C21 and range F15:F21) have a formula which gets values from another workbook.
Example off such a formula : =If(C14="";"";HLOOKUP(B15;'X:...\[File.xlsx]QryReport!$1:$23;16;0))

What the formula does is basically search the value in B15 in a file, in a certain location, and return the value in the 16th row.

Each month I get a new file (always the same format). When the user pushes the button a browser opens and the user selects the file he or she wants.
Thus the links in the formula needs to be changed.

For this, I have following code:

Please Login or Register  to view this content.

The code works perfectly but changes the links in the entire workbook. I just need the code to change the links in certain ranges.
Bassicaly, I need it to do the following:

• User pushes the button
• Browser opens
• User selects the first file
• Formula's are updated in range C15:C21 with the location of the first file (values are returned from the first file in range C15:C21)
• New browser opens
• User selects second file
• Links are updated in range F15:F21 with the location of the second file (values are returned from the second file in range F15:F21)

I already tried multiple adaptations to the code but I can't get it to do what I want.

Any help would be greatly appreciated .

[PCI]

Have you ever see the use of INDIRECT in your formulas for the range where it must changed ??

[Toetegaai]

Have you ever see the use of INDIRECT in your formulas for the range where it must changed ??

Thanks doe the reply PCI. I will look into the INDIRECT function to see if I can better my formula.

However I don't see how this will solve my problem with changing the link to another workbook.

I want my file to, in the range C15:C21, to reference a certain workbook selected by the user. The range C15:F21 needs to reference another workbook, different from the workbook in C15:C21,also selected by the user.

The problem with my code is that they links change in the entire workbook. When they second browser windows opens I get the values in C15:C21 and F15:F21 because when the linknchanged it changes in both ranges referencing the same workbook

[PCI]

Is it always range C15:C21 the range where to change file name in formula ??

[Toetegaai]

For anyone interested, I found a working solution to my problem.

I updated the code as such:

I put the formula's in another column, which I hid.

Then the code prompts the user for the first file (formula shows data) and copy and paste the value in the targeted cell.

It's a bit of a workaround but it works.