BJ, Yeah I have it working now...it was a simple typo...now I'm just trying
to figure out how to fill in the equation and have it skip a row in between
data.

"bj" wrote:

may seem like an odd question but do you have <tools<options<calculations
set to automatic.
if it is set to manual, when you drag the equation down, it will have the
answer from just the first cell in all of the others, until you tell it to
calculate.
the suggestions people have been giving should be working.

"MG" wrote:

Bernie,

I want the information from cells L46 on the other sheets "001", "002",
etc.. to go to column "I" of my first sheet "MIKE"

"Bernie Deitrick" wrote:

=INDIRECT(TEXT(ROW()-ROW($I$4),"000") & "!$L$46")

In your original post, I couldn't tell what your desired cell address was....

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
...
Didn't work. I have numerous tabs "001", "002", "003" etc and on each one of
them in column L and Row 46 I have information I want to transfer to column I
on my main page "Mike". When I fill in the equation down I want the row and
column part to remain and change the page number each time. Any other ideas?

"Bernie Deitrick" wrote:

Mike?,

If your sheet tabs are always three digit numbers, you could use, for example, this formula,
entered
into a cell I5:

=INDIRECT(TEXT(ROW()-ROW($I$4),"000") & "!$I$46")

Note that the cell reference (in this case $I$4) needs to be from the row just above the first
instance of the formula.

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
...
How can I change the tab but keep the row and column constant when filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.