capture cell info from different worksheets into a column of Summary Sheet

[hocs]

I have created a few identical sheets, namely Invoice, Invoice (2), Invoice (3) and so on. Cell F5 of each invoice will be the invoice number (running number). After sheet Invoice (3), i have created another sheet namely Invoice Summary (on the right to Invoice (3)), which will have one of the column, says column C to capture invoice number from sheet Invoice, Invoice (2) and Invoice (3) by draging down column C2 to C4 of Invoice Summary sheet:

C2 to reflect invoice number of sheet Invoice
C3 to reflect invoice number of sheet Invoice (2)
C4 to reflect invoice number of sheet Invoice (3)

however, relative sheet reference could not be perform by draging down the fill handle.
How do i solve this?
Thanks in advance!

 

[clipro]

in cell C3 put this formula and drag it down

=INDIRECT(CONCATENATE("Invoice";ROW(A3)-1;"!F5"))

cheers

 

[hocs]

in cell C3 put this formula and drag it down

=INDIRECT(CONCATENATE("Invoice";ROW(A3)-1;"!F5"))

cheers

Hi Clipro,

Thanks for your advice. It workd perfectly with the following amendments:
1. I rename sheets to invoice2, invoice3...... instead of Invoice (2), Invoice (3)...

2. =indirect(concatenate("Invoice",Row(A3)-1,"!F5"))

Thanks!