Creating a Formula using values from worksheets in two workbooks

[Dcdrj2]

I have a situation where I have two workbooks. Workbook A contains Work Order details on separate worksheets including a Work Order name. the Work Order Name is the worksheet name The Second Workbook is an Invoice Workbook with Invoices for each customer purchase. I am trying to create a formula in My Invoice workbook that takes the Work Order name in the Invoice Workbook to reference the Work Order Worksheet in the Work Order Work book. By Example on Work Order Workbook Worksheet named "David Bruce" I have a line item total Price in cell H12. In my Invoice workbook I have a Work Sheet that contains the work order Name "David Bruce" and I would like to create a Reference in the Invoice Work Book on the specific invoice that points to the Cell H12.

I have been able to create a direct reference ='[Work Order.xls]David Bruce'!$H$12 that does provide the correct result. What I am trying to do is Replace the specific Worksheet name "David Bruce" with a cell reference from the current the current workbook/worksheet by example D20.

Thank You

[TMS]

You need to research INDIRECT.

[Dcdrj2]

Excellent got it to work. thanks for the suggestion

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.