How to reference a different sheet with a long formula?

[djarcadian]

I have this long formula on Sheet4. The cells it needs to reference are on sheet Sheet3. How would I format this correctly?

Please Login or Register  to view this content.

[NBVC]

You would add the sheet reference followed by an ! sing in front of each range..

e.g. Sheet3!D104:H104

[djarcadian]

So there's no way to preface the formula by telling Excel that everything in this formula is from a different sheet?

[NBVC]

I'm afraid not... you have to identify where each range is.

[djarcadian]

Thanks anyways!

[Mark@Work]

You could use the MIN function rather than IF in your formula, thus:

MIN(SUM(D104:H104,D125:H125,D146:H146,D167:H167,D189:H189,D210:H210,D231:H231),40)

This will shorten the formula considerably (even with the addition of SHEET3! against each range).

Mark.