Change sheet reference automatically by dragging

[Marc Poos]

Hello,

I'm new here, so I hope I've chosen the correct forum for my question. My English is somewhat poor, so please tell me if there's something that you don't understand.

I have actually different sheets from "01" to "13" that contain monthly billings. There is one more sheet (called "part patronale") that should take values from these "01" to "13" sheets. I'd like to drag the formula vertically down with only the sheet reference changing, meaning that it should begin with referring to the sheet "01", than, by dragging, changing into "02", then "03" and so on.Can you help me with that please?

Here is my formula:
=ROUND(IF($C20>$E$15;$E$15*(2*$E$9)-'01'!$J$27;ROUND($C20*(2*$E$9);2)/2);2)

[benishiryo]

hi there, welcome to the forum. try:
=ROUND(IF($C20>$E$15;$E$15*(2*$E$9)-INDIRECT("'"&TEXT(ROW(1:1);"00")&"'!$J$27");ROUND($C20*(2*$E$9);2)/2);2)

if it doesnt work, do upload a sample file. uploading a sample Excel file in the thread will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

[Marc Poos]

Thank you very much benishiryo, that helped just the way I hoped! My inscription was yet woth it!!! :-)

By pure curiosity, could you tell me what part of your insertion tells me what sheet it refers to, as the formula always shows "00"?

[benishiryo]

always glad to help~

this is the portion:
INDIRECT("'"&TEXT(ROW(1:1);"00")&"'!$J$27")
basically inside the INDIRECT formula, i want the 1st cell to be '01'!J27, then '02'!J27, '03'!J27, & so on. so the only variable here is the worksheet name. to obtain that, i used the ROW formula. ROW(1:1) gives me 1. copying it down will change it to ROW(2:2) & returns 2.

but your worksheet name contains 2 digits, so i used the TEXT formula to say whatever the results of the ROW formula, return 2 digits.

"'"&TEXT(ROW(1:1);"00")&"'!$J$27"
so a combination of a single quote, ROW number with 2 digits, exclamation mark & J27 would give me a text of:
"'01'!$J$27"
this would just be a normal text & not a cell reference. so the INDIRECT formula with these texts will then make it an actual cell reference.

if you're satisfied with the answer, please mark it as "Solved". my signature in the thread will guide you how to do that. thanks
=)

[Marc Poos]

Again: Thank you!