Need help converting internal formula to an external reference

Formula:

Please Login or Register  to view this content.

I spent all day yesterday trying to figure out how to get this array formula above here to reference the noted Table locations, but in an external workbook. Currently, the formula works as long as that HardwareTable resides within the same workbook, but I need it to reference that HardwareTable "externally". Both workbooks would be open by the time that this formula would run.

I've tried replacing the Table reference with an indirect reference but that wasn't working. No errors, just not working.

Here's a couple of examples of my indirect attempts.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

These are arrays.

Thank you for any guidance or help with this. I'm starting to get cross-eyed staring at this for the past 8 hours.

-Mike

Without looking at this too hard so this is just a thought !

Have you tried inserting"

'[2ndWorbookName.xlsx](HardwareTable[PROJECT HARDWARE LIST] into your current formula or something along those lines ?

I have. But no luck.

I know how to access information within external workbooks in a number of different ways, but I can't figure this out.

I do appreciate your feedback tho BlindAlley. Thanks for trying.

Thanks Mike, I know the feeling, I had to post my own "tear my hair out" rant earlier :-)

I'm on my way out now but will take a bigger look later at this if it is not already answered.

A little more information on this...

I need to build my path to the external workbook table by using information from cells inside the active workbook.

Example:
Cell

Formula:

Please Login or Register  to view this content.

will contain a string of text that looks similar to this

Formula:

Please Login or Register  to view this content.

Inside of that Hardware Job Example.xlsx!Template file will contain the HardwareTable.

My objective is use that cell $A$7 to help fill in the path for my formula above so that the path can be dynamically updated based off of the information that feeds that cell.

Hopefully that made sense.

Originally Posted by mikerodrigueziii

Formula:

Please Login or Register  to view this content.

I spent all day yesterday trying to figure out how to get this array formula above here to reference the noted Table locations, but in an external workbook. Currently, the formula works as long as that HardwareTable resides within the same workbook, but I need it to reference that HardwareTable "externally". Both workbooks would be open by the time that this formula would run.

I've tried replacing the Table reference with an indirect reference but that wasn't working. No errors, just not working.

Here's a couple of examples of my indirect attempts.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

These are arrays.

Thank you for any guidance or help with this. I'm starting to get cross-eyed staring at this for the past 8 hours.

-Mike

So I figured it out. I had to do away with the referencing the "table" in the external sheet and instead indirect reference the range that the table resides in. Like all things that I can't seem to figure out, merely stepping away from it for the weekend, I was able to come in fresh minded and solve this problem on my own. Posting this thread did get me to think about what I was trying to achieve in a different way. So thank you "excelforum" for providing that outlet.

Here's the updated formula:

Formula:

Please Login or Register  to view this content.

$A$9 = a concatenated string which references the path/filename/sheet to where the "HardwareTable" data resides within column's C:D.

Thanks again to BlindAlley for at least responding to my initial request for help.

Consider this issue resolved.

-Mike in Cali