Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
the formula given is giving me error: "too many arguments". to make things easier. i changed the location of the shared file and even renamed it... but still it is a mystery to me.. seems i didnt learn the syntax. the name of the sheet is changing, in cell A5... while the Vlookup is as such: =VLOOKUP($A14,[_testDontOpenPlz.xlsm]Annie!$A:$U,3,FALSE) in this case, Annie! is the sheet which would be other times "Lara", or "Rima".... –  Annie Apr 29 '13 at 6:04
    
Sorry, forgot to close the Indirect() braces. Edited now. –  teylyn Apr 29 '13 at 7:44
    
dear. solved. it is working perfectly.. THANKS A LOT!!! –  Annie Apr 29 '13 at 13:14
    
@Annie, can you please click the tick to mark this as answered, then? It'll help others find questions with solutions. –  teylyn Apr 29 '13 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.