Keep reference to a cell when column is deleted

This is difficult to explain but is causing me no end of problems!

I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)

I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!

At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.

When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.

I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.

Is this Possible?


Hope that makes sense!

I have found a way to do this by using the INDIRECT FUNCTION which works a treat.

The only thing is pasting the formula is very long winded as it does not automatically update the formula as cell reference has to be in ""

Eg The formula will be: =INDIRECT("B5")

if I paste this it will copy the above exactly and not the dynamic formula =INDIRECT("B6") which I need!

Is this possible or is it a limitation of the function??

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!

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

Sometimes the old things are the best!

I have spent the last hour pulling teeth before I found this solution!!

EdMac you are a legend!