Fill in a formula within a worksheet

[Tracy]

I have the following formula which I need to fill across the worksheet but
the cell reference is from another sheet and the information within the other
sheet goes down the page not from left to right?

Colum A Column B
=+'CallList'A112 =+'CallList'A113


Tracy

 

[Pete_UK]

Put this in A1:

=INDIRECT("'CallList'!A"&(111+COLUMN(A1)))

Then you can copy this across.

Hope this helps.

Pee

 

[Roger Govier]

Hi Tracy

Try
=INDIRECT("'CallList'!A"&COLUMN(A112))

As you copy across the sheet , Column() will increase, to give you the
values from successive rows.

 

[Tracy]

It still does not incrementally go up by rows, it goes up by the Call List
columns.

 

[Tracy]

Don't know whether the last info went through, but the columns are
incrementally increasing, not by the rows from the Call List worksheet.

 

[Glenn]

It still does not incrementally go up by rows, it goes up by the Call List
columns.

Did you try the formula? What results did you get?

 

[Tracy]

I tried that, but it does not increase the rows, only the columns

 

[Pete_UK]

In A1 the formula will contain COLUMN(A1) which evaluates to 1, which
in turn is added onto A111 to make A112 in the CallList sheet. When it
is copied into B1 then that part of the formula will show COLUMN(B1)
which returns 2 and hence the reference is to A113 in the CallList
sheet. Similarly in C1 it will show COLUMN(C1), returning 3 and making
a reference of A114, and so on.

So, although the word COLUMN appears in the formula, this translates
into the appropriate ROW of the CallList sheet.

Try the formula - see what happens (put some values like A, B, C in
those consecutive cells A112, A113, A114 etc of the CallList sheet and
you should see A, B, C etc returned across the row).

Hope this helps.

Pete

 

[Tracy]

Roger,
Thank you for the information, but maybe I didn't put the question correctly.

I have a function in one worksheet which pulls information from another
worksheet
Worksheet "Call List" E817

In another worksheet I want to put the information from Call List E817 in
that cell and copy to the right that formula while increasing the row number
only from the Call List worksheet.. E817, E818, E819. Everytime I try to
copy or fill to the right column "E" changes and not the row.

 

[Glenn]

Roger,
Thank you for the information, but maybe I didn't put the question correctly.

I have a function in one worksheet which pulls information from another
worksheet
Worksheet "Call List" E817

In another worksheet I want to put the information from Call List E817 in
that cell and copy to the right that formula while increasing the row number
only from the Call List worksheet.. E817, E818, E819. Everytime I try to
copy or fill to the right column "E" changes and not the row.

Tracy: Please ignore what you THINK the formulas offered will do and try them
and see what they ACTUALLY do. You have successfully communicated what you want.

 

[Gord Dibben]

Works for me.

I entered =OFFSET(CallList!$A$112,COLUMNS($A:A)-1,) in A1 of a new sheet.

Dragged across to Z1 and returns the contents of CallList A112:A137


Gord Dibben MS Excel MVP

 

[Pete_UK]

Although now, Tracy, you have a difference set of cell references compared
to what you originally posted. Try this amended formula:

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

Note that the 816 gets added to COLUMN(A1) within the inner brackets, so
that returns 817, and that then gets joined on to "CallList!E" to form the
cell reference CallList!E817 - the INDIRECT function allows you to calculate
cell references in this way.

Hope this helps.

Pete