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???