excel formula questions (keyboard, Office, formulas, how to)

[sal02]

i have this formula ='sheet 1'!E9
when i drag it to next column it becomes ='sheet 1'!E10, 'sheet 1'!E11, etc
but i want it to become ='sheet 2'!E9, ='sheet 3'!E9, ='sheet 4'!E9 etc
how can i do this
please help

[Peregrine]

Try this. Fill in the first two.
Highlight BOTH cells and use the fill handle and drag to fill the remaining cells.

[jaypee]

use absolute reference $E$9 instead of just E9

[reed303]

Originally Posted by sal02

i have this formula ='sheet 1'!E9
when i drag it to next column it becomes ='sheet 1'!E10, 'sheet 1'!E11, etc
but i want it to become ='sheet 2'!E9, ='sheet 3'!E9, ='sheet 4'!E9 etc
how can i do this
please help

Here is how that works in OpenOffice. Perhaps it is same or similar in Excel. Easy once you try it.

" Copying to Multiple Sheets

*Copying to Multiple Sheets In OpenOffice Calc, you can insert values, text or formulas that are simultaneously copied to other selected sheets of your document.

• Select all desired sheets by holding down the Command key and clicking the corresponding register tabs that are still gray at the bottom margin of the workspace. All selected register tabs are now white.
  You can use Shift+Command+Page Up or Page Down to select multiple sheets using the keyboard.
• Now when you insert values, text or formulas into the active sheet, they will also appear in the identical positions in the other selected sheets. For example, data entered in cell A1 of the active sheet is automatically entered into cell A1 of any other seleted sheet."

[sal02]

none of these working when i use absolute reference and drag. it gives me ='sheet 1'!$E$3, ='sheet 1'!$E$3, etc all the way and i get exactly same value i dont need this i need it to become ='sheet 1'!$E$3, ='sheet 2'!$E$3, ='sheet 3'!$E$3, etc how can i do this


selecting all sheets also doesnt solve my problem

[reed303]

Yes, I see now what you are asking. You want something that acts the opposite of the $. You want the Sheet # to increment when copied, where it normally does not. That is the opposite of cell refs that normally do increment when copied, but the $ stops the incrementing. Sorry, there doesn't seem to be any modifier that accomplishes this action.

Others may know some way to do this.

[jaypee]

Sorry, I misread your question and only answered half of it.
You still want to use $E$9 so it doesn't increment.

For incrementing the worksheet, since you didn't mention how the data is laid out, look into how to use the INDIRECT function for your purpose.

[Treenoid]

An answer that I came to use often over the years -- >

Can't be done in this version !

I gave that answer before I even heard the question...

Sorry - I do not do much excel these days..

[reed303]

Originally Posted by reed303

Yes, I see now what you are asking. You want something that acts the opposite of the $. You want the Sheet # to increment when copied, where it normally does not. That is the opposite of cell refs that normally do increment when copied, but the $ stops the incrementing. Sorry, there doesn't seem to be any modifier that accomplishes this action.

Others may know some way to do this.

While wandering around the internets, came across a forum entry where user claims to have made it work.

copy formula - keep cell same but increment sheet numbers - ExcelBanter

Good luck

[sal02]

Originally Posted by reed303

While wandering around the internets, came across a forum entry where user claims to have made it work.

copy formula - keep cell same but increment sheet numbers - ExcelBanter

Good luck

still doesnt get it what does this means

"Put this in C1:

=INDIRECT("'"&ROW(A1)&"'!AB9")

then copy it down to C70.

Hope this helps.

Pete"