| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to make excel sheet generates an auto number | Excel Discussion (Misc queries) | |||
| How to make file links relative | Excel Discussion (Misc queries) | |||
| Copy a relative reference formula from one sheet to another. | Excel Discussion (Misc queries) | |||
| How do i make a macro "relative" to cell? | Excel Discussion (Misc queries) | |||
| relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Linear Mode
