Help with filling a formula across rows

Hi, I am an Excel newbie and require help with the following

I want to fill the following formula across rows but don't know how to get the bolded "1" and "1" in the formula to increase automatically to "2" and "2" then "3" and "3" and up 1 number each time as I fill it across the rows.

I want the cell references in the formula to stay the same and I am using this formula at the moment and then having to manually change the "1"s to "2"s and so on each time I fill across.

='1'!$B$9&" - "&'1'!$H$9

Please remember I am new to Excel when replying and perhaps you could give me a formula I can copy in as I know nothing about functions.


Many thanks for any help forthcoming.

When you say "across rows"
Do you mean you will fill the formula from A1 to B1 to C1 to D1 ?
or fill the formula from A1 to A2 to A3 to A4 ?

Hi Jonmo1, thanks again for your help yesterday.

Sorry, I meant across from a1 to b1 to c1 etc.

Try

=INDIRECT("'" & COLUMNS($A1:A1) & "'!B9")-INDIRECT("'" & COLUMNS($A1:A1) & "'!H9")

don't change the $A1:A1 references, they have no relation to your data.

Jonmo1 again you've helped me, many, many thanks.

The result should be "10 - 40" in the cell but i figured it out and just changed your formula to below as your original one was giving -30 not taking the - sign as a dividing space between numbers but as a mathematical minus

=INDIRECT("'" & COLUMNS($A1:B1) & "'!B9")&"-"&INDIRECT("'" & COLUMNS($A1:B1) & "'!H9")

Again THANK YOU so much

OH, I see.

I thought you wnted to subract H9 from B9...lol

looks like you figured it out though.

Glad to help out.