Copy a formula to the right increasing the rows from another works

[Tracy]

I posted this question this morning, but I did not get a resolve.

Maybe this is a little more clear.
I have two worksheets within my file

Worksheet called "Call List" and another called "Div 6"

Within the Div 6 worksheet I have a formula in cell (G7) which copies
information I already input from cell E817 from the Call List wksht. I now
want to fill and/or copy that formula in Div 6 G7 to all the cells adjacent
to the right while increaseing the formula from the Call List worksheet by
one row. So I am filling/copying a formula to the right from a worksheet
that has the information going down. Everytime I try to copy the formula the
column keeps increasing instead of the row, i.e E817, G817 when I want the
formula to read E817, E818, E819, ect.

Call List Worksheet Div 6 Worksheet
E G H I
817 bob 7 bob (from E817) E818 E819
818 ted
819 bill

 

[macropod]

Hi Tracy,

In G7, try:
=OFFSET($E$817,COLUMN()-7,)
then copy across as far as needed.

 

[Pete_UK]

Tracy,

please put this formula in G7 of the Div 6 sheet:

=INDIRECT("'CallList'!E"&(816+COLUMN(A1)))

then copy it across the row. You will get:

bob ted bill etc

as required.

Hope this helps.

Pete

 

[Gord Dibben]

Tracy

Same answers as you got to your earlier posting.

Please stick with one thread until you get satisfaction.

Several of the formulas already given will do what you want.


Gord Dibben MS Excel MVP

 

[Tracy]

Pete,

Thank you, thank you, thank you. Hours of work, gone in a flash of a formula.
Bloody Nora you made it seem easy. Many thanks. Cheers!!

 

[Tracy]

Pete, one more question if I can be so bold to ask.
Can you take this formula and make it able to copy across like the other
formula increaseing the cell 817 to 818, 819, etc..?

=IF('Call List'!$B$817=1,'Call List'!$D$817,"Not in Use")

 

[Pete_UK]

You're welcome, Tracy - I'm glad you gave it a go.

Pete

 

[Pete_UK]

Try this, then, Tracy:

=IF(INDIRECT("'Call List'!B"&(816+COLUMN(A1)))=1,INDIRECT("'Call List'!
D"&(816+COLUMN(A1))),"Not in Use")

The $ symbols are not required here as the references to B and D will
not change as they are text values.

Just copy this across as required.

Hope this helps.

Pete

 

[Tracy]

Once again Thank you Pete for all your help.

 

[Pete_UK]

That's okay - thanks for feeding back.

Pete