Link two excel sheet and have an input box on start-up

[mohsin002]

Hi

So I have two excel sheets, one which contains a database (named Price References) and the other one which has the search function (Named Search). However, I would like to have the database in a separate workbook and like the search sheet to exist in a different workbook.

Now the problem is that the location of the Search workbook will be dynamic (and so will change) so I want it so that when the database sheet is accessed, it asks for the location of the search workbook, stores and links the formula from the Search workbook into the database workbook by entering the correct location.

Here is the formula in the database workbook that requires a reference from the search workbook.

=IF(ISNUMBER(SEARCH('Search'!$C$4,C16)),MAX($E$1,B15)+1,0)

The 'Search' in quotations is the current sheet that exists in the same workbook.

I'm okay with using VBA or whatever to get this done. Just need a start.

Thanks
Mohsin

 

[mohsin002]

Nevermind...figured it out.