Convert MAX OFFSET to MAX INDEX or MAX LOOKUP

[Ravenskeep]

I have the following MAX OFFSET formula in one workbook that is referencing to another workbook. It returns a #VALUE! until the source workbook is open. Once opened data appears and is updated. I hoping there's a way to make it a MAX INDEX formula or some sort of MAX LOOKUP formula to resolve this issue, but I just can't seem to figure it out. Hope someone can help me.

=MAX(OFFSET('[C:\Desktop\Book1.xlsx]1'!$G$34,0,0,1,$A$1))<o></o>
<o></o>
Formula is in cell A3 for Book1.xlsx sheet 1; in cell A4 for Book1.xlsx sheet 2 and so on.<o></o>
<o></o>
Results – If I type 4 in A1 of Book2.xlsx; then it returns the max value within Book1.xlsx G34, H34, I34, J34 (below example – Answer 199). If I type 36; then it goes through AP34; which is the last cell with data (below example – Answer 241). Row 19 in BOOK1.XLSX does have numbers 1-36.<o></o>
<o></o>
BOOK2.XLSX

	A	B
1	4
2
3	=MAX(OFFSET('[C:\Desktop\Book1.xlsx]1'!$G$34,0,0,1,$A$1))
4	=MAX(OFFSET('[C:\Desktop\Book1.xlsx]2'!$G$34,0,0,1,$A$1))

<TBODY>
</TBODY>

BOOK1.XLSX Sheet 1

	G	H	I	J	K		AP
19	1	2	3	4	5		36
34	186	199	145	190	241		200

<TBODY>
</TBODY>

 

[Ravenskeep]

Ok, thought I had it but there's still a little glitch.

Came up with:
=MAX('[C:\Desktop\Book1.xlsx]1'!$G$34:INDEX('[C:\Desktop\Book1.xlsx]1'!$G$34:$AP$34,0,$A$1))

This works fine if both workbooks are open, but when only Book2 is open it returns the correct results when a 1 or 2 is input into $A$1 & the result stays without going back to #VALUE!. Great!! However; any other number (3 through 36 in this case) returns #REF!. Open Book1 a correct result appears. Close both, Open Book2 #REF! again.

Can someone please help?