Autofill Problems-Need parts of formula to fill different when pulling from 2nd sheet

Ok, I'll try to explain this as best I can.

I have a formula in X5: =C5-Sheet1!C5

I would like to paste this formula in X11.

When it is pasted in X11, I need the first part of the formula to autofill normally, making it jump up by 6 and turn into C11. However, since the second set of data is on a different sheet and spaced out differently, I need to second part of the formula to jump by 19

So the formula in X5: =C5-Sheet1!C5
Formula in X11 needs to be: =C11-Sheet1!C24

Basically, on the master sheet, the I'm pasting the formula 6 rows below and want the data from that sheet corresponding to 6 below my current formula..this works fine with autofill.

However, since the data on the 2nd sheet is further apart, autofill does not work because it changes the 2nd part of the formula by 6 instead of the 19 that I need from the 2nd sheet.

I hope I explained this ok.

Any suggestions?

No ideas on this? Would it be easier to do with a macro or VBA programming? I can do this manually, but I need to fill the formula for 50+ rows and 8 columns...that is going to be a ton of work.

Hoping someone could take a stab on this. Let me know if I didn't explain this well enough or you need more info.

Thanks

*bump*

This is the best resource I've found to answer these type of questions..

I've been working with OFFSET to make this work but haven't found a working answer.

Hoping someone could take a stab at this.

Thank you so much for your help. Both work perfectly. I've been working for hours trying to figure this out.

Now for the tough part:

How do I modify this formula to point at different cells on each of the sheets?

For example, in X5 I had: =C5-Sheet1!C5
I needed in X11=C11-Sheet1!C24

This works correctly with either of the formulas posted.

However, some other examples both below and next to this formula:

X6: =C6-Sheet1!C15
I need X12: =C12-Sheet1!C34

This is the same example as the last one, but when I tried to copy and paste the above formula below, it didn't work. Neither did fill.

Another example:

Y5: =J5-ABS(Sheet1!H5)
I need Y11: =J11-ABS(Sheet1!H24)

In this example, the column rows are different for the first and second parts of the formula (in the first, they were both in C column on different sheets)

Finally,

Z5: =L5-ABS(Sheet1!L5)
I need =L11-ABS(Sheet1!L24)

This is the exact same example as the first one but even if I change all of the "C" in the formula that was given to "L", it still doesn't return the correct formula.

Once again, I hope I explained myself. Thank you for the work so far. I will continue playing around with this in the hopes of figuring it out.


This last example is exactly like the original