On the 'fill down' command Excel allows the increase by 1 for cell reference
Exm: if one fills dowm from cell A1 which referes to cell B1, the result in
cell A2 will refer to cell B2.

This does not happen with reference to Sheets. Sheet numbers do not increase.

Is it possible to add this option?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi Albert,
One way of achieving the same effect is to use INDIRECT and ROW
functions. For example, say you want a column on Sheet1 to show each
sheet's A1 value. This can be achieved by placing the following formula
into any row 1 cell (other than column A) then filling down so that
the number of cells with the copied formula equals the number of
worksheets:

=INDIRECT("Sheet" & ROW() & "!A1")

Ken Johnson

Hi Ken,

Thank you for your suggestion. I will try it and let you know regarding the
result.

Much obliged

Regards

Albert

"Ken Johnson" wrote:

Hi Albert,
One way of achieving the same effect is to use INDIRECT and ROW
functions. For example, say you want a column on Sheet1 to show each
sheet's A1 value. This can be achieved by placing the following formula
into any row 1 cell (other than column A) then filling down so that
the number of cells with the copied formula equals the number of
worksheets:

=INDIRECT("Sheet" & ROW() & "!A1")

Ken Johnson

Hi Ken,

Thank you very much for your suggestion, but either I misunderstood the
formula or it did not work. You should know that I am self thought so I
could be a bit slow in understanding. I am writing the formula I have so if
you can, please write the formula for me with the same references.

=IF('0001'!$E$3="","",IF('0001'!$E$30,'0001'!$E$3 ))

'0001' is the sheeet name

Thank you very much for your concern

Regards

Albert

"Ken Johnson" wrote:

Hi Albert,
One way of achieving the same effect is to use INDIRECT and ROW
functions. For example, say you want a column on Sheet1 to show each
sheet's A1 value. This can be achieved by placing the following formula
into any row 1 cell (other than column A) then filling down so that
the number of cells with the copied formula equals the number of
worksheets:

=INDIRECT("Sheet" & ROW() & "!A1")

Ken Johnson

Hi Albert,
I must have just missed your last message last night. I'm in Sydney
Australia and got to bed at 3 am, the time on your message is 3:05 am.
Hope you're not in a rush.
In its simplest form the formula is:

=IF(INDIRECT(LEFT("'000",4-INT(LOG(ROW()-1))) & ROW()-1
&"'!$E$3")="","",IF(INDIRECT(LEFT("'000",4-INT(LOG(ROW()-1))) & ROW()-1
&"'!$E$3")0,INDIRECT(LEFT("'000",4-INT(LOG(ROW()-1))) & ROW()-1
&"'!$E$3")))

There are a couple of important thing to keep in mind:

1. It uses the ROW() function to increment the referencing as you fill
down the column you are using for this formula

2. I have assumed row 1 is needed for a heading, consequently, as it
stands it will only start referencing the first worksheet (0001) if it
is pasted into row 2 before the fill down. The reason being, in row 2
the function ROW() returns the number 2, then when the 1 is subtracted
you end up with 1, (ROW() -1 = 2, for row 1).
If you don't have a heading and you must start in row 1 then just
delete every "-1".
If you need to start further down the column than row 2 then replace
every "-1" with "-n", where n is one less than the row number
of the row in which you are starting the formula eg if you must start
in row 3 then the formula in row 3 should be:

=IF(INDIRECT(LEFT("'000",4-INT(LOG(ROW()-2))) & ROW()-2
&"'!$E$3")="","",IF(INDIRECT(LEFT("'000",4-INT(LOG(ROW()-2))) & ROW()-2
&"'!$E$3")0,INDIRECT(LEFT("'000",4-INT(LOG(ROW()-2))) & ROW()-2
&"'!$E$3")))

You might be able to make the formula more fail-safe and portable by
replacing those numbers with a formula that counts the rows above your
starting row (I'm not too sure), but it's already complex enough.


3. A large part of the formula's complexity is due to the way the
sheets are numbered with the leading zeros. If there were no leading
zeroes the formula (starting in row 2) would just be:

=IF(INDIRECT("'" & ROW()-1 & "'!$E$3") = "", "",
IF(INDIRECT("'" & ROW()-1 & "'!$E$3") 0,INDIRECT("'" &
ROW()-1 &"'!$E$3")))

4. If you fill down further than there are worksheets you will see the
#REF! error in the cells that have exceeded the number of worksheets,
but that's no big deal.

5. When reading the formula the '' and ' are easily confused.
Where you see "' it's " followed by '. Where you see ""
it's " followed by ".

Sorry for being slow and long winded. Even before I realised there
would be a problem with the leading zeros I thought I was going to have
a problem getting the formula to result in a blank when $E$3 is blank.
You see, say A1 has =INDIRECT(B1) and B1 contains the address C1 then
A1 will show 0 when C1 is blank. As it turned out this problem does not
occur when using INDIRECT the way we have here.
Hope this makes sense! I too am self taught and unfortunately I'm a
lousy teacher and an even worse student

Ken Johnson

Hi Albert,
Just a little correction in my blurb. In my second point I wrote (ROW()
-1 = 2, for row 1),
which should have been (ROW() -1 = 1, for row 2)
Ken Johnson

Hi Ken,
Thank you once again, I will try it even though it seems so complicated. I
will let you know the result.

Regards
Albert

"Ken Johnson" wrote:

Hi Albert,
Just a little correction in my blurb. In my second point I wrote (ROW()
-1 = 2, for row 1),
which should have been (ROW() -1 = 1, for row 2)
Ken Johnson

Hi Albert,
Good Luck
Ken Johnson