Set up a formala to refer to a cell in a different sheet

[Brian]

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

[Vacation's Over]

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
>

[Brian]

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
> >

[Vacation's Over]

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
> > >

[WightRob]

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