I need to set up a formula in one sheet to appertain to a cell in another
sheet(same row, different column) that can be dragged and copied down a
column. I have found that =INDIRECT("'"&$D$19&"'!b"&ROW()) , where D19 is the
name of sheet 2, does what I want as long as I don't insert a new row in
sheet 1. However, whenever I insert a new row in sheet one, all of the
information is thrown off. Can I somehow lock the rest of the column against
the information listed in column A, (for example A23 is account number,
B23-G23 is other info related to that account), but still have a formula that
can be copied without using a VBA macro?? If so, how?
Thanks

Why not just

=Sheet1!b23

relative refernces adjust to "most" move, insert, delete operations

"Brian" wrote:

I need to set up a formula in one sheet to appertain to a cell in another
sheet(same row, different column) that can be dragged and copied down a
column. I have found that =INDIRECT("'"&$D$19&"'!b"&ROW()) , where D19 is the
name of sheet 2, does what I want as long as I don't insert a new row in
sheet 1. However, whenever I insert a new row in sheet one, all of the
information is thrown off. Can I somehow lock the rest of the column against
the information listed in column A, (for example A23 is account number,
B23-G23 is other info related to that account), but still have a formula that
can be copied without using a VBA macro?? If so, how?
Thanks

The problem is that I now have over 30 different sheets. I was hoping this
was possible using an indirect reference so I could type the name of the
sheet in one cell and have the whole sheet update with info from the most
recent sheet 2. I would use =INDIRECT("" & $D$19 &"!C5")..or whatever cell I
need, but that would have to be entered differently in each row. Is there no
way I can do this by entering one formula and dragging it down the column??

"Vacation's Over" wrote:

Why not just

=Sheet1!b23

relative refernces adjust to "most" move, insert, delete operations

"Brian" wrote:

I need to set up a formula in one sheet to appertain to a cell in another
sheet(same row, different column) that can be dragged and copied down a
column. I have found that =INDIRECT("'"&$D$19&"'!b"&ROW()) , where D19 is the
name of sheet 2, does what I want as long as I don't insert a new row in
sheet 1. However, whenever I insert a new row in sheet one, all of the
information is thrown off. Can I somehow lock the rest of the column against
the information listed in column A, (for example A23 is account number,
B23-G23 is other info related to that account), but still have a formula that
can be copied without using a VBA macro?? If so, how?
Thanks

http://office.microsoft.com/en-us/as...549021033.aspx

look at using Match and VLookup together as in this link

"Brian" wrote:

The problem is that I now have over 30 different sheets. I was hoping this
was possible using an indirect reference so I could type the name of the
sheet in one cell and have the whole sheet update with info from the most
recent sheet 2. I would use =INDIRECT("" & $D$19 &"!C5")..or whatever cell I
need, but that would have to be entered differently in each row. Is there no
way I can do this by entering one formula and dragging it down the column??

"Vacation's Over" wrote:

Why not just

=Sheet1!b23

relative refernces adjust to "most" move, insert, delete operations

"Brian" wrote:

I need to set up a formula in one sheet to appertain to a cell in another
sheet(same row, different column) that can be dragged and copied down a
column. I have found that =INDIRECT("'"&$D$19&"'!b"&ROW()) , where D19 is the
name of sheet 2, does what I want as long as I don't insert a new row in
sheet 1. However, whenever I insert a new row in sheet one, all of the
information is thrown off. Can I somehow lock the rest of the column against
the information listed in column A, (for example A23 is account number,
B23-G23 is other info related to that account), but still have a formula that
can be copied without using a VBA macro?? If so, how?
Thanks

I think that I have had the same problem using INDIRECT as you, where
the data inside the quotes (!C5 in your example) is not modified when
inserting rows or copying and pasting. I solved this by filling column
A with the ROW() function. You then need to replace "!C5" with "!C"&A5.
If the formla is then copied to row 6 for example, the reference
becomes "!C"&A6 whereas "!C5" would remain "!C5".

Hope this helps.

Rob


--
WightRob
------------------------------------------------------------------------
WightRob's Profile: http://www.excelforum.com/member.php...o&userid=13799
View this thread: http://www.excelforum.com/showthread...hreadid=477948