how do i make a sheet number relative in a formula
 

how do i make a sheet number relative in a formula?
I am trying to copy a cell down a column. My formula brings in data from
another Excel file.
Everything needs to stay the same as I drag/copy the formula except for the
"sheet number"

For me each cell looks like this when I copy it down:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3

I would like it to look like this:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet2'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet3'!$B$3

Thanks in advance

how do i make a sheet number relative in a formula
 

Hi,
unfortunately you can not change the sheet number just draging it, you can
use find and replace to do that after the formula has been copied. Press CTRL
H

"DCook" wrote:

how do i make a sheet number relative in a formula?
I am trying to copy a cell down a column. My formula brings in data from
another Excel file.
Everything needs to stay the same as I drag/copy the formula except for the
"sheet number"

For me each cell looks like this when I copy it down:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3

I would like it to look like this:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet2'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet3'!$B$3

Thanks in advance

how do i make a sheet number relative in a formula
 

DCook wrote:
how do i make a sheet number relative in a formula?
I am trying to copy a cell down a column. My formula brings in data from
another Excel file.
Everything needs to stay the same as I drag/copy the formula except for the
"sheet number"

For me each cell looks like this when I copy it down:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3

I would like it to look like this:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet2'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet3'!$B$3

Thanks in advance


=INDIRECT("'Untitled:July Billing:[Billing Invoice.xlsx]Sheet"
&ROWS($1:1)&"'!$B$3)")

how do i make a sheet number relative in a formula
 

=indirect('Untitled:July Billing:[Billing Invoice.xlsx]Sheet" & row(A1) &
"'!$B$3")

If this post helps click Yes
---------------
Jacob Skaria


"DCook" wrote:

how do i make a sheet number relative in a formula?
I am trying to copy a cell down a column. My formula brings in data from
another Excel file.
Everything needs to stay the same as I drag/copy the formula except for the
"sheet number"

For me each cell looks like this when I copy it down:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3

I would like it to look like this:
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet1'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet2'!$B$3
='Untitled:July Billing:[Billing Invoice.xlsx]Sheet3'!$B$3

Thanks in advance