Excell dragging formula to change sheet/tab not column/row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MG - 18 Jun 2007 20:52 GMT

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 to this Message

FSt1 - 18 Jun 2007 21:36 GMT

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.

> 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
[quoted text clipped - 6 lines]
> 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 to this Message

krcowen@aol.com - 18 Jun 2007 21:50 GMT

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

> hi,
> personally I seldom use the drag function for formulas but this function
[quoted text clipped - 13 lines]
>
> - Show quoted text -

Reply to this Message

T. Valko - 18 Jun 2007 21:42 GMT

Try it like this:

=INDIRECT(TEXT(ROWS($1:1),"000")&"!I46")

Biff

> I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in.
> I'm
[quoted text clipped - 11 lines]
> 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 to this Message

Gord Dibben - 18 Jun 2007 22:35 GMT

=INDIRECT("00" & (ROW()) & "!$I$46") entered in any column in row 1 and copied
down that column.

Gord Dibben MS Excel MVP

>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
[quoted text clipped - 6 lines]
>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 to this Message