Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excell dragging formula to change sheet/tab not column/row
I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in. I'm
trying to put a formula in on my main page where I can get the data from many different sheets while keeping my column and row constant on each page. I'm trying to do this so I can drag this down and it will automatically switch pages and while keeping column and row constant. Example, my main tab/sheet is labeled "TODAY" and I want to put information from many different tabs/sheets in column "I" on the main page. Right now I've got the formula ='001'!$l$46...and when I drag it down I want it to pick up sheet '002'!$l$46 for the next line, '003'!$l$46 for the line after...etc. Is there anyway I can do that?? If you could help me out I'd greatly appreciate. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excell dragging formula to change sheet/tab not column/row
hi,
personally I seldom use the drag function for formulas but this function works on cells and not sheets. I know of no where in excel where you can change this. "MG" wrote: I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in. I'm trying to put a formula in on my main page where I can get the data from many different sheets while keeping my column and row constant on each page. I'm trying to do this so I can drag this down and it will automatically switch pages and while keeping column and row constant. Example, my main tab/sheet is labeled "TODAY" and I want to put information from many different tabs/sheets in column "I" on the main page. Right now I've got the formula ='001'!$l$46...and when I drag it down I want it to pick up sheet '002'!$l$46 for the next line, '003'!$l$46 for the line after...etc. Is there anyway I can do that?? If you could help me out I'd greatly appreciate. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excell dragging formula to change sheet/tab not column/row
Try it like this:
=INDIRECT(TEXT(ROWS($1:1),"000")&"!I46") Biff "MG" wrote in message ... I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in. I'm trying to put a formula in on my main page where I can get the data from many different sheets while keeping my column and row constant on each page. I'm trying to do this so I can drag this down and it will automatically switch pages and while keeping column and row constant. Example, my main tab/sheet is labeled "TODAY" and I want to put information from many different tabs/sheets in column "I" on the main page. Right now I've got the formula ='001'!$l$46...and when I drag it down I want it to pick up sheet '002'!$l$46 for the next line, '003'!$l$46 for the line after...etc. Is there anyway I can do that?? If you could help me out I'd greatly appreciate. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excell dragging formula to change sheet/tab not column/row
MG
You will probably have to build your linked formula within an Indirect function. If you have in column A, 001, 002, 003, 004, ... you can put in column B the following formula =INDIRECT("'"&A1&"'!$a$1") and copy it down it will return the value from cell A1 on each of the sheets named 001, 002, 003, ... Good luck. Ken Norfolk, Va On Jun 18, 4:36 pm, FSt1 wrote: hi, personally I seldom use the drag function for formulas but this function works on cells and not sheets. I know of no where in excel where you can change this. "MG" wrote: I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in. I'm trying to put a formula in on my main page where I can get the data from many different sheets while keeping my column and row constant on each page. I'm trying to do this so I can drag this down and it will automatically switch pages and while keeping column and row constant. Example, my main tab/sheet is labeled "TODAY" and I want to put information from many different tabs/sheets in column "I" on the main page. Right now I've got the formula ='001'!$l$46...and when I drag it down I want it to pick up sheet '002'!$l$46 for the next line, '003'!$l$46 for the line after...etc. Is there anyway I can do that?? If you could help me out I'd greatly appreciate.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excell dragging formula to change sheet/tab not column/row
=INDIRECT("00" & (ROW()) & "!$I$46") entered in any column in row 1 and copied
down that column. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 12:52:26 -0700, MG wrote: I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in. I'm trying to put a formula in on my main page where I can get the data from many different sheets while keeping my column and row constant on each page. I'm trying to do this so I can drag this down and it will automatically switch pages and while keeping column and row constant. Example, my main tab/sheet is labeled "TODAY" and I want to put information from many different tabs/sheets in column "I" on the main page. Right now I've got the formula ='001'!$l$46...and when I drag it down I want it to pick up sheet '002'!$l$46 for the next line, '003'!$l$46 for the line after...etc. Is there anyway I can do that?? If you could help me out I'd greatly appreciate. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do column widths change in Excell? | Excel Worksheet Functions | |||
How do I change the sheet tab font in Excell 2003 PRO | Setting up and Configuration of Excel | |||
formula/check for doubling in column in Excell sheet | Excel Worksheet Functions | |||
dragging a formula down a column | Excel Worksheet Functions | |||
insert query into excell sheet to update excell sheet and pivot table | Excel Discussion (Misc queries) |