No idea?!

[jackdiamond]

Hi,

We have a spreadsheet where in cells A1:A100, we have dates for the year which are fixed.

In cells B1:B100 we enter in random dates depending on the schedule etc.

If you imagine for example that in cell A3, I have the date 23/09/11 and in cell B3 i have 10/10/13. These 2 entries need to stay next to each other.

If I then insert cells above B3, it will push all the cells below down, thus splitting up the dates that I need to keep together.

Is there anyway that I can link the two dates so they stay together throughout the spreadsheet please?

Any help appreciated

ta
Stu

 

[Ruddles]

Insert an entire row, not just one cell.

 

[jackdiamond]

Apologies, I have explained it completlely wrong without a vital part of info!

Imagine cells A1:A100 have fixed dates.
Cells B1:B100 can have dates entered.

An example:
Cell A1 has the date 23/09/11
Cell B1 is referenced to A1 so that it reads the same (23/09/11)

When I insert a cell above B1, it will push it down but still reference A1 but I now want it to reference A2 or which ever number of cells it has been pushed down by.

Does this make sense?

So however many cells "B1" moves down, I want it to then reference whatever cell it is adjacent to in the "A" column

 

[Joe4]

Ruddles is right. It will automatically do that if you insert entire rows, not just individual cells. Are you sure you mean to just insert individual cells and not rows? That is not something I see people do all that often.

 

[jackdiamond]

yes, it is individual cells i need to insert.

It is a running schedule of work so sometimes work gets pushed down the queue. So if I inserted a whole new row, it would push the "fixed" dates down too (column A). I dont want them to move

 

[Joe4]

Try this formula. This will return the value of column A in that same row, no matter what row it gets pushed down to (so if this formula gets pushed down to B4, it will return what is in A4):
=INDIRECT("A" & ROW())

 

[jackdiamond]

Thank you Joe4!!

That is exactly what I needed and apologies for the duplicate post earlier


Ta
Stu

 

[shg]

Or =INDEX(A:A, ROW())

 

[Joe4]

Your welcome!