vlookup and indirect and external shared workbooks

Question

i have two excel workbooks that are both shared in our company folder.

excel1 is where each employee has his own page to fill information and column 1 of each page is the row reference

excel2 is a form to be filled and printed. but information is taken from excel1's pages.

ideally, colleagues are to fill their name in cell A5 of excel2, which is same as their sheet in excel1............ example: excel1 has sheets named: sheet1="Annie", sheet2="lara", sheet3="maya", sheet4="liza"........... so liza will write liza in cell A4 in excel2 so the information from liza's excel1 sheet will be filled in the form excel.

the logic is as follows:

in excel2 =vlookup(a14, in excel1 sheet liza, 3, false).. if liza is in cell A5 in excel2 = vlookup(a14, in excel1 sheet Annie, 3, false)... if Annie is in cell A5.

the actual formula of the vlookup is as such: =VLOOKUP(A14,'[______current shared PO.xlsm]Annie'!$A:$U,3,FALSE)

i tried the indirect function, but reached walls. if i won't be able to find a solution to this, i will be obliged to create form sheets for each employee in Excel2.

i am using Excel2010 and strong note: both excels must be shared so many people can use it at the same time.

is my request possible???

Answer 1

It is possible. At least for a while. You can never be sure how long it will last, though. Shared workbooks, i.e. workbooks accessed by multiple people at the same time are notoriously unreliable, will eventually become corrupt and impossible to troubleshoot. If you want to use shared workbooks, you better have a very good backup regime.

For the Indirect() formula to work on an external file, the other file must be open. Then you can use something like

=VLOOKUP(A14,Indirect("'[excel1.xlsm]"&A5&"'!$A:$U"),3,FALSE)

You may want to consider building a solution the other way around, i.e. each user has her/his own workbook and nobody else uses it. Then create another workbook for reporting purposes that pulls from all the user workbooks with lookup formulas.

Or, if you really need simultaneous multi-user access, consider a database like Access or SQL. You can still use Excel as the user front end for data input and reporting.