Prev: Counting frequency in unsorted data
Next: Consolidate excel 2007
From: kimmyhendrix on 11 Apr 2010 22:47

Hi everyone, my first time on the forum and my first thread. I apologize
if this is already located on the forum here. I tried searching, but
came up with many unrelated threads so I thought I'd give this a shot.

I am trying to auto complete a link to another worksheet by corner
dragging a formula over a ROW in one worksheet FROM data in the COLUMN
of another worksheet. In other words:

Worksheet 1: E9, F9, G9, should point to (respectively),
Worksheet 2: C2, C3, C4

The auto complete formula function does not recognize what I'm trying
to do. And I have to do this with tens of thousands of cells. It would
boil down to only a hundred rows or so if I could automate this.

Thanks for any help!

- excel noob -




--
kimmyhendrix
From: ozgrid.com on 12 Apr 2010 03:16
Based on your example;
=INDIRECT("'Sheet2'!C" & COLUMN()-3)



--
Regards
Dave Hawley
www.ozgrid.com
"kimmyhendrix" <kimmyhendrix.608777a(a)excelbanter.com> wrote in message
news:kimmyhendrix.608777a(a)excelbanter.com...
>
> Hi everyone, my first time on the forum and my first thread. I apologize
> if this is already located on the forum here. I tried searching, but
> came up with many unrelated threads so I thought I'd give this a shot.
>
> I am trying to auto complete a link to another worksheet by corner
> dragging a formula over a ROW in one worksheet FROM data in the COLUMN
> of another worksheet. In other words:
>
> Worksheet 1: E9, F9, G9, should point to (respectively),
> Worksheet 2: C2, C3, C4
>
> The auto complete formula function does not recognize what I'm trying
> to do. And I have to do this with tens of thousands of cells. It would
> boil down to only a hundred rows or so if I could automate this.
>
> Thanks for any help!
>
> - excel noob -
>
>
>
>
> --
> kimmyhendrix

From: Roger Govier on 12 Apr 2010 09:22
Hi
As an alternative to Dave's suggestion, especially if there are going to
be a lot of formulae on the sheet, you could use the non-volatile Index
function.
Enter in E9
=INDEX(Sheet2!$1:$65536,COLUMN()-3,ROW()-6)
Copy across and down as required

--
Regards
Roger Govier

kimmyhendrix wrote:
> Hi everyone, my first time on the forum and my first thread. I apologize
> if this is already located on the forum here. I tried searching, but
> came up with many unrelated threads so I thought I'd give this a shot.
>
> I am trying to auto complete a link to another worksheet by corner
> dragging a formula over a ROW in one worksheet FROM data in the COLUMN
> of another worksheet. In other words:
>
> Worksheet 1: E9, F9, G9, should point to (respectively),
> Worksheet 2: C2, C3, C4
>
> The auto complete formula function does not recognize what I'm trying
> to do. And I have to do this with tens of thousands of cells. It would
> boil down to only a hundred rows or so if I could automate this.
>
> Thanks for any help!
>
> - excel noob -
>
>
>
>
 | 
Pages: 1
Prev: Counting frequency in unsorted data
Next: Consolidate excel 2007