autofill INDIRECT formula

[stuck]

I want to autofill an INDIRECT formula, say:
    =INDIRECT("Sheet1!B6")

However, I can't use the usual trick of making the cell ref. dynamic by concatenating a ROW() function that returns the relevant number, like:
    =INDIRECT("Sheet1!B"&ROW())
This is because the block of INDIRECT formulae will be sorted and I want the INDIRECT to find the same value before and after the sort.

At the moment I'm using a helper column that holds numbers that correspond to the required row numbers, thus my INDIRECT becomes:
    =INDIRECT("Sheet1!B"&A1)

Does any one know of a way of avoiding the helper column? NB I don't want a VBA solution.

Thanks,

Ken

[rory]

Is the formula on Sheet1 or a different sheet? And what is the need for INDIRECT?

[stuck]

The INDIRECT is on Sheet2, the value it is getting is on Sheet1.

I'm using this construct so I can create a template that includes formulae that point to sheet that does exist in the template but then work as required when a sheet of the appropriate name is copied into a workbook created from the template.

Ken

[rory]

Ah, that last bit stops my solution I suspect. Is there another sheet in the template that won't ever get sorted? Basically, if you can use a different sheet for your ROW() formula, it wouldn't be affected by sorting the sheet with the formula.

[stuck]

No, each sheet in the book sorts and charts the raw data that's on the sheet that gets added in a different way.

The helper column solution that I've already got sounds like a variation on your suggestion. It's not affected by the sort so it does what I want. I just wondered if I was missing something obvious or if more experienced Excel brains knew of a clever way of doing it all in one formula. I'm encouraged that it doesn't seem like I have missed anything or that there is a 'better' way of doing this

Thanks,

Ken