Keeping cell references constant

[Guest]

Hi,

I'm creating a multisheet spreadsheet where I need to keep the number
reference constant.

Example of formulae:

=+'A Sheets'!H18

I want the cell letter to increase but the number to remain constant. So
copying down the spreadsheet the next reference would be:

=+'A Sheets'!I18 then =+'A Sheets'J18 etc. etc.

I've tried using '$' but to no avail.

Thanks in advance.

Sean.

 

[Pete_UK]

You could try this - make the formula:

='A Sheets'!H$18

then copy it across the row so that the H becomes I, then J, then K
etc.

Then highlight all these cells, click <copy>, move cursor down one cell
and Edit | Paste Special | Transpose (tick) | OK and <Esc>. You could
then delete the row with the original formulae in.

Hope this helps.

Pete