Indirect concatenate cell reference resulting in error

Me again, with a new problem it's been stumping me for hours

I am referencing cells in a sheet within a document. Nice and simple =Sheet1!A1 (that sort of thing) My first problem - which I solved - was when I copied the formula into another workbook, it drags the original workbook file name in so it doesn't point to the new sheet. So it changed the formula to ='[original worksheet.xlsx]Sheet1'!A1

I resolved this by populating a cell with the current file name using =CELL("filename") and forcing my cell reference formula to look at the file name first, then the cell reference using INDIRECT(CONCATENATE()). This works nicely when I paste both the file name and cell ref formulas into another workbook.

My file name is in cell B2. The data I want to reference is in a sheet called 'Membership List' and the data starts on D17, but I also care about the data down to D200.

This is the formula with the hard-coded cell reference which works for the single row: =INDIRECT(CONCATENATE("'[",$B$2,"]Membership List'!$D17"))

However, my new problem is that I want to drag this cell reference formula down to other rows, and have the cell reference dynamically change the row number. But, when using the indirect concatenate functions, I am hard-coding the cell reference in using quotes. Which means dragging down the formula is simply replicating cell D17. So, i'm trying to take the cell reference outside the concatenate part of the formula, so it changes when you drag it down.

I'm getting a #VALUE! error with my attempt to do this, and i'm stuck on how to fix this - if even I can.

This is my formula where I try to pull the cell D17 outside the formula: =INDIRECT(CONCATENATE("'[",$B$2,"]"),'Membership List'!$D17)

Is there a way around this?

If you only want to copy the formula down, then it is only the row reference that will change, so try it this way:

=INDIRECT(CONCATENATE("'[",$B$2,"]Membership List'!$D",ROWS($1:17)))

Note that you could avoid using the CONCATENATE function by using the concatenation operator ( & ), like this:

=INDIRECT("'[" & $B$2 & "]Membership List'!D" & ROWS($1:17))

which makes it a bit shorter.

Note also that INDIRECT only works with files that are open, so you need to ensure that is the case.

Hope this helps.

Pete

Thanks for the rep.

Pete