Find and Replace problem

[mark99th]

Hi All,

I'm trying to help a friend sort out a spreadsheet problem. He has a sales spreadsheet for each order of product he receives, he then wants to consolidate the quotation data into 1 master spreadsheet.

I tried INDIRECT, but this only deals with open spreadsheets, and he doesn't want to open all of them just to get the figures, then lose them once they are closed, so now I am trying to reference the file as +'[XXXXX.xlsm]Data Sheet'!$B$8 where XXXXX is the unique, incremented quote number.

Here's the problem. I can't find a way in the master spreadsheet to automatically replace the XXXXX with the quote number from Column A. What (I think) I need is a find / replace macro to get the quote number from Column A and then replace the XXXXX in that row cells D through V. There will be hundreds of quotations in the future so it has to also keep going until it finds the last quote number

I'm not that Excel savvy, and little experience of Macro's or VBA (except copy paste existing code)

Example attached Test Data.xlsx

If I need to clarify anything, please let me know.

Thanks in advance for anyone who can help.

Mark

[dilipandey]

Hi Mark,

Welcome to the forum.

I suggest you to use the names in column A in Indirect function to pick up the values.. but yes, you need to open those workbooks to fetch the numbers..

Regards,
DILIPandey

<click on below 'star' if this helps>