pasted links

When I paste a link from one sheet to another, it shows up as =MAIN!$B$211. Then when I drag this across a range of cells, it stays the same. I want it to change to =MAIN!$B$211, =MAIN!$C$211, =MAIN!$D$211, etc. I know I can get rid of the dollar signs before dragging and that will do it but I want to put the $ back in. Is there a way to toggle the $ on and off for a cell or a range of cells? Thanks.

Bre

You would need to put the $ back in manually or by using the replace command

Ctrl + H

replace =MAIN!
with =MAIN!$

make sure you check the settings under Options and Look In is set to Formulas

I thought there was a way to highlight a range and toggle $ on or off. I seem to remember doing it. First time it would toggle rows, 2nd time columns or something similar.

Bre

I have seen posts in various forums that mention on how to do this.

After searching various posts it looks like you need to have a look at the INDIRECT function.


I have no experence in this using Indirect but have a look at http://www.cpearson.com/excel/indirect.htm