Using the ROW() command

I have the following formula that uses the ROW() command.
=HYPERLINK("["&M16&ROW()&O16&"]")
M16 cell value = PC_
The formula is held in cell A16, so the ROW() command report back the value 16
and 016 cell value = .xlsx
The resulting hyperlink = PC_16.xlsx (which loads the file PC_16.xlsx)

I am trying to use the same ROW() command in another formula but all I get is the #REF! error can anyone please tell me where I’m going wrong?
='Z:\Product Change\["&M16&ROW()&O16&"]Product Change Form '!$D$45
I’ve even tried a different variation of the same command
='Z:\Product Change\[PC_"ROW()&".xlsx]Product Change Form '!$D$45
I am trying to report back the text value of a cell D45 in the same file PC_16.xlsx. If I write the formula as below it works
='Z:\Product Change\[PC_16.xlsx]Product Change Form'!$D$45

The reason I am using the ROW() command is because I need to cut and paste the formula to many cells and I want the formula to increment up (the file that it is looking at) without me having to manually edit each formula
for example PC_16.xlsx – PC_17.xlsx – PC_18.xlsx and so on, and I was hoping to use the ROW() command to report back the row number so that I could link it to a file with the same number. Alternatively if anyone has any other suggestions to how I can achieve the same goal I would be very grateful. I’ve been stuck on this last hurdle for 4 days now and it’s preventing me from completing my project.

You would normally use INDIRECT to do this. However, that function does not work with closed workbooks. An alternative would be to download the free add-in called Morefunc, which has the function INDIRECT.EXT which will allow you to use closed workbooks - do a Google serch to find sites where it can be downloaded from. The syntax for using it would be:

=INDIRECT.EXT("'Z:\Product Change\["&M16&ROW()&O16&"]Product Change Form '!$D$45")

Hope this helps.

Pete

Hi Pete
Thank you for your help, after a lot of searching I found and downloaded morefunc.
I tried your formula
=INDIRECT.ext("'Z:\Product Change\["&M16&ROW()&O16&"]Product Change Form'!$D$45")
But all I get is #Name? Error.
How can I tell if morefunc has installed properly and is working.
I’m running windows 7 (64bit) and office 2010 (32bit)
I’ve read online that is works on windows 7 and office (32bit)

Hi Pete
Thank you for your help, after a lot of searching I found and downloaded morefunc.
I tried your formula
=INDIRECT.ext("'Z:\Product Change\["&M16&ROW()&O16&"]Product Change Form'!$D$45")
But all I get is #Name? Error.
How can I tell if morefunc has installed properly and is working.
I’m running windows 7 (64bit) and office 2010 (32bit)
I’ve read online that is works on windows 7 and office (32bit)

Well, #NAME implies that it hasn't been installed properly, as that function name is not being recognised. Perhaps it doesn't work on later versions of Excel.

I don't know what else to advise.

Pete

Hi Pete

Problem solved I switched on the developer tab, selected add ins and pointed to the two morefunc files and hey presto!

Thank you for all your help

Peche

Ah well, that's good to hear. I don't have XL2010, so it's good to know that it does work with that, as I do recommend it to those posters who need it.

Pete