Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2020, 09:28 AM
hiwire03 hiwire03 is offline Formula for Cross Referencing Numbered List Windows 7 32bit Formula for Cross Referencing Numbered List Office 2013
Novice
Formula for Cross Referencing Numbered List
 
Join Date: Dec 2014
Posts: 17
hiwire03 is on a distinguished road
Default Formula for Cross Referencing Numbered List

I have a table with auto numbered rows.

Requirement.


(a) A Formula in the last column that must reference to a selected auto numbered row in the first column. Later, in case a row or column is inserted, the reference must change automatically to match the new value i.e. relative referencing.

(b) Optionally, if the reference is clicked on, it must jump to the particular target row referenced.

Regards
Attached Images
File Type: jpg Query Word Referencing.JPG (76.6 KB, 10 views)
Attached Files
File Type: docx Query Word Referencing.docx (15.0 KB, 1 views)
Reply With Quote
  #2  
Old 08-08-2020, 12:12 PM
Charles Kenyon Charles Kenyon is offline Formula for Cross Referencing Numbered List Windows 10 Formula for Cross Referencing Numbered List Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,155
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

For your numbering in your first column, you could use numbering tied to a style and use that style for the first column.

You might be able to combine columns A and B, if you wanted.

How to create numbered headings or outline numbering in Ribbon Versions of Word by Shauna Kelly
Reply With Quote
  #3  
Old 08-08-2020, 12:55 PM
hiwire03 hiwire03 is offline Formula for Cross Referencing Numbered List Windows 7 32bit Formula for Cross Referencing Numbered List Office 2013
Novice
Formula for Cross Referencing Numbered List
 
Join Date: Dec 2014
Posts: 17
hiwire03 is on a distinguished road
Default

Quote:
Originally Posted by Charles Kenyon View Post
For your numbering in your first column, you could use numbering tied to a style and use that style for the first column.

You might be able to combine columns A and B, if you wanted.

How to create numbered headings or outline numbering in Ribbon Versions of Word by Shauna Kelly
Unfortunately, the above link does not address the requirement. My first column is already numbered automatically like I mentioned earlier.

What is required is a Excel like solution to referencing. The cell of a particular row in the last column must reflect the value of the cell of another row in the first column.
Reply With Quote
  #4  
Old 08-08-2020, 02:25 PM
Charles Kenyon Charles Kenyon is offline Formula for Cross Referencing Numbered List Windows 10 Formula for Cross Referencing Numbered List Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,155
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

My suggestion does address your problem but you would need to revise your numbering.

Here is a temporary link to the file with the following tables:
Dropbox - deleteme Table Cross-Reference.docx - Simplify your life






Anything else I could do is going to be far less automatic. You could always insert an Excel table. Word is not Excel, nor vice versa.
Reply With Quote
  #5  
Old 08-08-2020, 07:39 PM
Guessed's Avatar
Guessed Guessed is offline Formula for Cross Referencing Numbered List Windows 10 Formula for Cross Referencing Numbered List Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 2,086
Guessed is a name known to allGuessed is a name known to allGuessed is a name known to allGuessed is a name known to allGuessed is a name known to allGuessed is a name known to all
Default

You are asking for a Cross-Reference functionality but don't want to use that functionality. Why? This is exactly what you should be using.

Using an Excel-like formula's whereby you specify a cell address won't give you a cell reference that automatically changes if that cell moves. Word just doesn't work the same as Excel. Also, if you wanted it to by a hyperlink then you should be using Cross-Refs which do that already and takes care of both requirements at once. And finally, because you are using autonumbers in that source cell, there is no 'content' that a cell formula would display. If you typed the numbers rather than using an autonumber there would be content that could be displayed in a field eg {=Sum(A4)}
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 08-08-2020, 08:31 PM
hiwire03 hiwire03 is offline Formula for Cross Referencing Numbered List Windows 7 32bit Formula for Cross Referencing Numbered List Office 2013
Novice
Formula for Cross Referencing Numbered List
 
Join Date: Dec 2014
Posts: 17
hiwire03 is on a distinguished road
Smile

Quote:
Originally Posted by Charles Kenyon View Post
My suggestion does address your problem but you would need to revise your numbering.

Here is a temporary link to the file with the following tables:
Dropbox - deleteme Table Cross-Reference.docx - Simplify your life






Anything else I could do is going to be far less automatic. You could always insert an Excel table. Word is not Excel, nor vice versa.
Thanks a ton, placing the number in the same cell with the text worked well and fulfills both the requirements though a separate column for numbers would be ideal . My apologies for not deliberating sufficiently on your initial reply, I now understand what you intended to convey.

Regards
Reply With Quote
  #7  
Old 08-08-2020, 08:39 PM
hiwire03 hiwire03 is offline Formula for Cross Referencing Numbered List Windows 7 32bit Formula for Cross Referencing Numbered List Office 2013
Novice
Formula for Cross Referencing Numbered List
 
Join Date: Dec 2014
Posts: 17
hiwire03 is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
You are asking for a Cross-Reference functionality but don't want to use that functionality. Why? This is exactly what you should be using.

Using an Excel-like formula's whereby you specify a cell address won't give you a cell reference that automatically changes if that cell moves. Word just doesn't work the same as Excel. Also, if you wanted it to by a hyperlink then you should be using Cross-Refs which do that already and takes care of both requirements at once. And finally, because you are using autonumbers in that source cell, there is no 'content' that a cell formula would display. If you typed the numbers rather than using an autonumber there would be content that could be displayed in a field eg {=Sum(A4)}
Thanks for the reply. Manually entering the numbers makes the formula work well. It is simple and straight forward except it does not fulfill the the requirements of relative referencing and hyperlinking. Any modification to field code that could fulfill the requirements?

Regards
Reply With Quote
  #8  
Old 08-08-2020, 08:54 PM
Charles Kenyon Charles Kenyon is offline Formula for Cross Referencing Numbered List Windows 10 Formula for Cross Referencing Numbered List Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,155
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

Look at my sample document again. It does what you are requesting.
The first table has separate cells for the number and the text. The second shows that you do not need to have that, although you can.
Both tables have REF fields for the cross-references.

The cross-reference fields there can be hyperlinks. They are linking to the numbered paragraphs. The fields in my document are hyperlinks. That is the default setting when inserting a cross-reference using the References tab.

Like all cross-reference fields, they do NOT update automatically when the source changes. They need to be updated, at which time the information in them will refresh.
Reply With Quote
  #9  
Old 08-08-2020, 11:36 PM
hiwire03 hiwire03 is offline Formula for Cross Referencing Numbered List Windows 10 Formula for Cross Referencing Numbered List Office 2019
Novice
Formula for Cross Referencing Numbered List
 
Join Date: Dec 2014
Posts: 17
hiwire03 is on a distinguished road
Default

Quote:
Originally Posted by Charles Kenyon View Post
Look at my sample document again. It does what you are requesting.
The first table has separate cells for the number and the text. The second shows that you do not need to have that, although you can.
Both tables have REF fields for the cross-references.

The cross-reference fields there can be hyperlinks. They are linking to the numbered paragraphs. The fields in my document are hyperlinks. That is the default setting when inserting a cross-reference using the References tab.

Like all cross-reference fields, they do NOT update automatically when the source changes. They need to be updated, at which time the information in them will refresh.
I had not updated. Now it works PERFECTLY
Reply With Quote
  #10  
Old 08-09-2020, 12:01 PM
Charles Kenyon Charles Kenyon is offline Formula for Cross Referencing Numbered List Windows 10 Formula for Cross Referencing Numbered List Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,155
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

You are welcome.
Reply With Quote
Reply

Tags
cross referencing, field code, reference links

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Step referencing in a numbered table TomBrady Word 1 10-27-2015 03:41 PM
Formula for Cross Referencing Numbered List Referencing something that isn't numbered (eg table, figure etc) hanvyj Word 2 04-10-2012 03:30 AM
Formula for Cross Referencing Numbered List issues with cross-referencing khaines Word 7 03-15-2012 03:25 AM
Referencing Previous Numbered List QUL001 Word 0 03-18-2010 12:14 AM
Cross referencing maltesedog Word 0 02-13-2009 07:43 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:28 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft