Auto-fill a sheet reference
 

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

Auto-fill a sheet reference
 

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" wrote:

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

Auto-fill a sheet reference
 

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" wrote:

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" wrote:

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

Auto-fill a sheet reference
 

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

"JOJO193" wrote:

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" wrote:

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" wrote:

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

Auto-fill a sheet reference
 

On Thursday, April 17, 2008 1:48:01 PM UTC-5, JOJO193 wrote:
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 these invoices 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!

Hi Yes I'm looking for this function as well

I am looking for cells $A$1, but from many different sheets

So I want it to be

Sheet1!$A$1
Sheet2!$A$1
Sheet3!$A$1
Sheet4!$A$1
..etc etc

the fill handle can't seem to figure out that it is the sheet numbers that are changing and nothing else