Auto-fill a sheet reference

[JOJO193]

I am trying to auto fill a sheetreference.

Right now I have ='Loan 37'!$G$14 and want excel to copy down ='Loan
38'!$G$14,='Loan 39'!$G$14 , ='Loan 40'!$G$14, and so on. Excel does not
recognize the pattern. How do I get it to do this?

Thanks

 

[pdberger]

I think your problem is your use of the '$', which defines what Excel terms
an absolute reference. That character means, "When I copy this formula,
don't change this part of the address." So the '$G' means "don't change the
G", and the '$14' means "don't change the 14". Sounds like you want
'relative references', so remove the '$' and try it.

 

[JOJO193]

PDBERGER, I want the cell to be an absolute reference but I want the sheet
to be a relative reference. I'm thinking it might not be possible. Let me know

 

[pdberger]

Sorry, misread.

Here's what I got to work. In cell 'Sheet1'!A1, I put a '1'. In cell
'Sheet2'!A1 I put a 2. In cell 'Sheet3'!A1 I wrote the following formula:

=INDIRECT("'Sheet" & ROW(A1) & "'!$A$1")

Note the double- and single-quotes before the Sheet and the !$A$1. When I
copied it down, it advanced from one sheet to the next, keeping the cell
reference constant. You can adjust the formula for your sheet names and
specific cells.

HTH

 

[bartendersunny1]

I am trying to auto fill a sheetreference.

Right now I have ='Loan 37'!$G$14 and want excel to copy down ='Loan
38'!$G$14,='Loan 39'!$G$14 , ='Loan 40'!$G$14, and so on. Excel does not
recognize the pattern. How do I get it to do this?

Thanks

I'm trying something similar, if you can help please let me know.

Ok, I'm making an invoice tracker, I use a template to create the invoices and I fill in and name each sheet the invoice # ex. IN12345 (and I place the name of the invoice into the sheet in cell A1). I keep all of theseinvoices in a folder labeled invoices. I need to get certain info off of them and placed on a common sheet. I need the spreadsheet I'm making to auto populate the info each time I drop a new invoice in the invoice folder. I can't get it to change the invoice # automatically. I can paste the formulas all the way down.

Also I have a range of cells that I need it to sum in addition to change the reference of the name of the sheet.

I also need a column in this tracker to update the date of which the line row was added to the sheet.

Any help is highly appreciated!