+ Reply to Thread
Results 1 to 8 of 8

Dynamic part of the text - changing the number of the row in a phrase

  1. #1
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Dynamic part of the text - changing the number of the row in a phrase

    Hello!

    I have a table and a note above the table. The note tells the user to look at the row 7 for certain information.

    But there is always a possibility that some rows will be added before row 7 thus moving the content from previously row 7 to row 9, for example.

    What type of formula can I use in this case?
    I was thinking about the OFFSET, but honestly as a beginner I have now idea how to integrate that function into the text.

    I am attaching a file also.

    Thank you very much!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Dynamic part of the text - changing the number of the row in a phrase

    How about:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will show "Look for instructions in row 7", but if you insert a row before row 7, it will read, "Look for instructions in row 8".

    If you want it to reference a particular cell, then you can use:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will display, "This will reference cell C7..."
    If you add a row and a column before C7, it will show "This will reference cell D8...".
    Last edited by SlipEternal; 07-20-2017 at 05:16 PM.

  3. #3
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Dynamic part of the text - changing the number of the row in a phrase

    Thank you very much! This does a wonderful job. Thank you for teaching me one more great trick.
    The only question: what does "$" do in the SUBSTITUTE formula, used in the place of the new text.

    SUBSTITUTE(text,old_text,new_text,instance_num)

    SUBSTITUTE(ADDRESS(ROW(C7),COLUMN(C7)),"$","")

    Usually when we surround a value with "" in Excel, it means some text value, correct? But how does it work here?
    Thank you!
    Last edited by AliGW; 07-22-2017 at 04:28 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,647

    Re: Dynamic part of the text - changing the number of the row in a phrase

    Vitalite - 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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Dynamic part of the text - changing the number of the row in a phrase

    AliGW, Ok, got it. )

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,647

    Re: Dynamic part of the text - changing the number of the row in a phrase

    The formula

    ADDRESS(ROW(C7),COLUMN(C7))

    will return $C$7, so to get rid of the $, you use

    SUBSTITUTE(ADDRESS(ROW(C7),COLUMN(C7)),"$","")

    which tells Excel to substitute the $ signs in the text string with "" (i.e. nothing), so you end up with the text string C7.

  7. #7
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Dynamic part of the text - changing the number of the row in a phrase

    Quote Originally Posted by AliGW View Post
    which tells Excel to substitute the $ signs in the text string with "" (i.e. nothing), so you end up with the text string C7.
    Aha, I see. Thank you very much! )

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,647

    Re: Dynamic part of the text - changing the number of the row in a phrase

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] part number data not fully dynamic throughout workbook
    By reveleth in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 03-07-2017, 12:44 PM
  2. [SOLVED] Summarize amount dependend on part number (dynamic)
    By joejonson in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-15-2015, 11:30 AM
  3. Replies: 4
    Last Post: 03-12-2013, 06:37 AM
  4. [SOLVED] how to detect a part of a phrase?
    By kello99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2012, 10:05 AM
  5. Function to return text from phrase X to phrase Y
    By razr in forum Excel General
    Replies: 3
    Last Post: 05-06-2009, 05:52 PM
  6. [SOLVED] How do I identify cells that contain part of phrase?
    By T Perry in forum Excel General
    Replies: 3
    Last Post: 05-23-2006, 03:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1