extenging function

[John]

hi!


I have big list of worksheets in one book. Every sheet is named by date and
in every sheet there are some number in exact same place. Now I need to
create a list of number in new sheet. For instance I have the number in cell
"B4" in every worksheet. For decreasing my time to copy and paste from
hundreds of sheets I need excel to do it for me.

If I drag from first function excel takes new sheet but same cell

like this:
='0510'!B4
='0511'!B4
='0512'!B4
='0513'!B4
='0514'!B4

and so on.

is it possible?

 

[bj]

try the indirect function
I assume the 0510 is a date
=indirect("'05"&(row()+adder)&"'!B4")
where the adder is the number necessaary to add to the row number to make it
be the day you are interested in

alternately you could put in a columns with the name of the sheets
=indirect("'"&sheetrange cell&"'!B4")

 

[John]

I have sheets named by dates from beginning of year as "MMDD"
like: 0501, 0502, 0503, 0504 and so on and on.
And in every sheet at cell B4 is the needed number.

=indirect("sheetname!B4) takes correct number, but I can't drag it down..

I also recive correct number if I write ='MMDD'!B4, bu now I want to drag it
down to have MMDD+1, MMDD+2 automaticly!

There is a limit with sheet names as well, sheets are created only at
workdays.. Monday to Friday. (no Saturdays and Sundays.)


"bj" kirjutas:

 

[Bob Phillips]

=INDIRECT("05"&TEXT(ROW(A1),"00")&"!B4")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bj]

in a column (A?)
in A2 enter the date of the firsst sheet
in A3 enter
=A2+IF(WEEKDAY(A2)=6,3,IF(WEEKDAY(A2)=7,2,1))
and drag down as far as you want
this will enter a list of weekday dates
in B2 enter
=indirect(text(A2,"mmdd"&"!B4")
Drag down as far as you want