If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Basic Question on autocomplete function
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 |
#2
|
|||
|
|||
Basic Question on autocomplete function
Based on your example;
=INDIRECT("'Sheet2'!C" & COLUMN()-3) -- Regards Dave Hawley www.ozgrid.com "kimmyhendrix" wrote in message ... 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 |
#3
|
|||
|
|||
Basic Question on autocomplete function
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 - |
Thread Tools | |
Display Modes | |
|
|