Keep reference to a cell when column is deleted

Hi

You can do it on the basis below but you will need to adjust the ROW statement if the information in the two sheets is not in the same row e.g. ROW()+5

=INDIRECT("your_sheet!Your-column"&ROW())

Ed

Thanks for the help

Have tried that but cant quite get it to work.

What does the &row function do?

row() returns the row number of the cell where the function is in

the "&" concatenates the column letter in th thus obtained row number

so on row 5 row() will return 5, and if in col A the outcome will be A5

Thanks

I'm starting to understand a bit more now but,

It appears to be selecting data from my sheet as opposed to the worksheet I am trying to extract the data from!

I've figured it out!

Thanks so much!

I've done lots of browsing to get help on this spreadsheet but thats the 1st time i've posted.

Thanks for your quick responce to help me out!

Maido!

More Trouble

Having more trouble now

As I am selecting from cells that jump 5 rows each time I cant seem to get it to paste and give me the results!!

Aaaargh

Hi,

can you post the sheet (ZIP it first) - then it's easier for everyone to see what the problem is.

Ed

This function builds up the cell address you are going to reference

your_sheet!Your-column is the sheet name and column e.g. DATA!A

the &ROW() is to get the row number to complete the cell address

If you don't add anaything to it, it will taker the row number that you are writing the formula in, say row 2, but the data you are looking to call in may be in row 10. In that case you would need to put +8 after the row reference.

Ed