re:"for the values to be visibile in the target workbook(not "#ref"), the target workbook must be open"
I hope this is a typo for "the source workbook must be open" or some such, otherwise the solution would be too easy!

Anyway, there are at least 2 solutions that I can think of, but which one's better depends on how the data you want to see is distributed, both in the source and destination worbooks:

If both the source and destination ranges are a few blocks of cells then a few querytables that can be refreshed at will is probably easier to maintain. Named ranges and whole sheets, some tables/lists, even autofiltered ranges in a closed workbook can be queried by another excel workbook.

On the other hand, since direct references (not involving the INDIRECT function) in cells can return values from closed workbooks, we get a macro to write these formulae into the cells, using values from cells C2, C2, C3 etc. These formulae, can be dotted about the destination worksheet, as can the cells they refer to in the source workbook.

So which is it?