Keep the references after sorting

[mastertonn]

Take a look at the spreadsheet.

The references will change after sorting and break the calculations.
Absolute referencing is not an option. The table has to be copied periodically to another sheet and placed it at a different row (the columns are identical). Absolute referencing will break the calculations after copying.

I would like to keep the references unchanged. One idea is to use formulas to maintain references.
For example, S75 contains:
=T70
Change it into:
=(the address of the Count data cell)

What formulas could I use to achieve this?

If no formula could do, I need to resort to macro. Use absolute referencing ($T$70) first. Then run a macro which change all selected cells into relative referencing (T70) every time it needs to be copied. Only the reference of T70 needs to be changed, not any others. I don't know how to code. Could anyone help? Thanks a lot.

Other links:
https://www.mrexcel.com/forum/excel-...r-sorting.html

[FDibbins]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

[mastertonn]

It has been included. It hasn't been solved yet. Thanks.

[FDibbins]

Rather than post a link to an online sight, which does not allow editing, upload a small (clean) sample workbook (not a pic) here, of what you are working with, and what your expected outcome would look like.

[mastertonn]

I believe you could choose to download the copy when you visit the online worksheet.

How could I upload a worksheet file here? I clicked on the attachment icon but it does nothing. It's weird.
Thank you for your help.

[AliGW]

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mastertonn]

Thank you. The file has been attached.

[shg]

Add the sheet references to the formulas and sort as you like:

	N	O	P	Q	R	S	T	U	V	W
69
70						Count	5
71	ID	Title	Title	Title	Title	Low	High	Title
72	1	xxx	xxx	xxx	xxx	2	9
73	2	xxx	xxx	xxx	xxx	10	11
74	3	xxx	xxx	xxx	xxx	3	15
75	4	xxx	xxx	xxx	xxx	6	8	A	S75: =Table!T70+1	T75: =Table!S75+2
76	5	xxx	xxx	xxx	xxx	5	6.5	B	S76: =Table!T70	T76: =Table!T70+1.5

[mastertonn]

Thank you for your reply.
It is similar to using absolute referencing. It could solve the issue of sorting. However it will be broken when you copy the table to another sheet.

[JeteMc]

Since the columns are identical, perhaps the following will help.
1) For cell S76: =INDEX(T:T,MATCH("Count",S:S,0))
2) For cell S75: =INDEX(T:T,MATCH("Count",S:S,0))+1
3) For cell T76: =INDEX(T:T,MATCH("Count",S:S,0))+1.5
4) For cell T75: =S75+2 (since the formula in this cell only references the cell to its immediate left)
Let us know if you have any questions.

[mastertonn]

Thank you for your reply. Unfortunately it does not work since the table will periodically copied to another sheet. There are more than one instance of the "Count" cell in the another sheet (whose name is "record" in the sample spreadsheet).

[JeteMc]

The sample workbook attached to post #1 only has sheets named 'Table', 'Sheet2' and 'Sheet3'. It may help us find a resolution if you could provide a sample that is closer to a desensitized/non proprietary version of the actual workbook.
Let us know if you have any questions.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[mastertonn]

OK thanks for the reminder, AliGW.

[mastertonn]

The sample workbook attached to post #1 only has sheets named 'Table', 'Sheet2' and 'Sheet3'.

The spreadsheet stored online has the "record" sheet. You could take a look at:
https://1drv.ms/x/s!Alp_NMIveFajft1a7RtdYJ930Bc

Ignore the attached file in the first post. I cannot update the attached file anymore.
Thank you for your help.

[JeteMc]

Perhaps something similar to the following might work:
1) Turn the range N71:U76 into a table,
2) Populate S76 using: =INDIRECT(ADDRESS(ROW(Table1[[#Headers],[High]])-1,20))
3) Since the values of the other cells can be derived based on the value of S76:
4) For S75 use: =INDEX([Low],MATCH("B",[Title5],0))+1
5) For T75 use: =INDEX([Low],MATCH("B",[Title5],0))+3
6) For T76 use: =INDEX([Low],MATCH("B",[Title5],0))+1.5
To test the table and accompanying 'Count' cells were copied into the Record sheet replacing the cells in range N10:U16
Let us know if you have any questions.