Need help with formula (consistency issue)

[leopardhawk]

Hello forum friends, need advice on a formula that can handle a row being deleted on another worksheet. To explain...

I have a worksheet where each row in column B contains a letter/number, C1 through C500, as a reference. Each cell in column B also contains a formula, ="C"&ROWS(B$6:B6) so that if an entire row is deleted, the rows below will all be renumbered in sequential order.

On another worksheet, users 'can' enter C1 through C500 in column K as a way to cross-reference the first worksheet. A problem arises if the user 'deletes' an entire row on the first worksheet which is not related to this cross-reference, thereby changing the letter/number of the cross-reference. This 'deleting' of rows is perfectly acceptable and in fact encouraged in this application but what I am hoping is that there is a simple way to have the cross-reference remain consistent once it's been entered on the other worksheet.

Thanks for any tips or suggestions!

 

[GlennUK]

In the other sheet, ensure the reference C1 through C500 (which you say users 'can' enter) are created as direct references to the main sheet, to the actual cell where the reference is. The reference of the formula will adjust when rows are deleted, and the result will update when references renumber.

 

[leopardhawk]

@GlennUK Thank you for helping. I think I may not have explained it very well. I'm not saying your post won't work but I'm not sure how to apply it. I'm going to explain my dilemma differently and if you still tell me your post will work, that's great. I might need a bit of help though with the implementation.

As stipulated, Column B on the first worksheet contains a letter/number, C1 through C500 and each cell has an underlying formula. Each row on the first worksheet contains data related to a shipment of goods from point A to point B.

Each row on the second worksheet also contains data related to a shipment of goods from point A to point B with column K containing blank cells and no formulae. The idea is to enable the user to 'occasionally' enter a cross-reference to the first worksheet if the shipment on the second worksheet would be a good candidate for a return trip or back-haul. So, most of the time, the cells in column K will be blank.

Example: while viewing the second worksheet, the user finds a shipment that will work well as a return trip for a shipment on the first worksheet in the row that contains the C10 reference, and enters 'C10' in cell K18 on the second worksheet. Later, on the first worksheet, row 8 is deleted so now the shipment that was formerly known as C10 becomes C9, but cell K18 on the second worksheet still shows C10. I would like it to now show C9 so that the continuity stays the same. This is what I am hoping to accomplish anyway. If it's not even possible, then I will move on but I'm thankful for the advice.

 

[jtakw]

Hi,

If I understand correctly, GlennUK's suggestion would work.
So, in the second sheet, instead of entering C10 in K18, enter =Sheet1!C10
or type = then click on sheet 1, select C10, then enter.

When rows are deleted in sheet 1 (except for row 10), sheet 2 K18 will auto adjust.

 

[leopardhawk]

@GlennUK @jtakw that's awesome! Thanks to both for your help!

 

[jtakw]

You're welcome, thanks for the feedback, glad we can help.