Change sheet reference OF ANOTHER WORKBOOK automatically by dragging

[Marc Poos]

Hello again,

As you helped me yesterday very quickly, I try my luck again.

I have different sheets from "01" to "13" that contain monthly billings in a workbook called "Salaire Welter.xlsx" (in fact I have 4 of those workbooks, just differently called). I'd now like to copy these values into a new workbook with only the sheet reference changing by dragging the formula horizontally to the left.

This is the cell I'd like to refer:

='[Salaire Welter.xlsx]01'!$J$40

It's the "01" that should change to "02" then to "03" and so on.

I'd be very grateful for your precious help.

[Pete_UK]

You can use this formula in the first cell:

=INDIRECT("'[Salaire Welter.xlsx]"&TEXT(COLUMNS($A:A),"00")&"'!$J$40")

and then copy it across. You should note, though, that INDIRECT does not work with closed workbooks, so you must ensure that the file Saltaire Welter.xlsx is open in the same instance of Excel as the file that contains the formula.

Hope this helps.

Pete

[mnjofra]

Hi Marc Poos,
you can try with this code

Please Login or Register  to view this content.

Please note that you have to change value of COLUMN() if your formula has not placed in column A.
For example COLUMN() in column A evaluate as 1, in column B evaluate as 2 etc. If you possibly place this formula in column F and you need evaluation 1 you have to change "COLUMN()" with "COLUMN()-5"

Regards,

[Marc Poos]

Hello Pete,

Thank you, but unfortunately, when copy/pasting your formula in my cell, Excel says "the formula you typed contains an error".

Marc

[Marc Poos]

Hello mnjofra,

Thank you, but unfortunately, I get, as it does for Pete's code, the message that "the formula (...) contains an error" when copy/pasting it.

Marc

[mnjofra]

Hi Marc Poos,
I dont know what international settings of Windows you have. Possibly you have to change comma <,> with semicollon <;> in formula if you have decimal comma instead of decimal point in your domestic standard.. In Croatia we have the same problem.

Regards,

[Pete_UK]

Hello Pete,

Thank you, but unfortunately, when copy/pasting your formula in my cell, Excel says "the formula you typed contains an error".

Marc

Try it like this instead:

Please Login or Register  to view this content.

Hope this helps.

Pete

[Marc Poos]

It worked!!!!

Thank to both of you!!

[mnjofra]

I'm glad, seems you're very happy

[batakman]

Hi Pete Uk, mnjofra and Marc Poos

I have the same problem too, but the formula cannot work.

='[shoes.xlsx]art1'!$v$11

It's the "art1" that should change to "art2" and so on.

I'd be very grateful for your precious help.
Thanks...

[Pete_UK]

@batakman

If you take the time to read the Forum Rules at the top of the screen you will see that you should not post a question in someone else's thread - instead, you should start your own thread.

Pete