+ Reply to Thread
Results 1 to 8 of 8

Substitute reference in cell formula with another reference.

  1. #1
    Registered User
    Join Date
    04-16-2021
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    7

    Substitute reference in cell formula with another reference.

    Hello everyone

    I have found a Data Validation formula that works well with what I need. However, the formula exceeds the 255 character limit. So I am pasting this formula on a cell on another sheet in the same workbook. So in the Data Validation field, I would like to substitute the formula with the appropriate cell reference for each cell to be validated.

    This is the validation formula that exceeds 255 characters:

    Please Login or Register  to view this content.
    Where xxx is to be substituted with D2, D3, and so on.

    In the Data Validation field, I typed

    Please Login or Register  to view this content.
    However it evaluates to a #NAME? error.

    How do I replace the formula with the appropriate cell reference for each cell to be validated?
    Last edited by harpbelle; 04-17-2021 at 12:57 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,463

    Re: Substitute reference in cell formula with another reference.

    What exactly "xxx" be? a cell reference?
    I believe SUBSTITUTE establishs a text string, not a formula to work in Data Validation.
    Post a small piece of sample worksheet.

  3. #3
    Registered User
    Join Date
    04-16-2021
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Substitute reference in cell formula with another reference.

    Yes, xxx is a cell reference.

    I've attached a sample.

    Column B would be the validation check for the column A.


    I could simply type =B2 in the Validation field, however, the validation formula fills up all the rows and causes my file to become very big and becomes a memory hog. I thought it would save much more space and memory to just refer to a single cell with a formula and replace the formula with the appropriate cell reference
    Attached Files Attached Files
    Last edited by harpbelle; 04-16-2021 at 11:40 PM.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,463

    Re: Substitute reference in cell formula with another reference.

    In actual file, is List alphabet characters from a-z?

  5. #5
    Registered User
    Join Date
    04-16-2021
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Substitute reference in cell formula with another reference.

    Yes. The last 3 characters of the text entry must be alphabets.

    Anyway, I've managed to solve the issue by trimming down the formula to less than 255 characters. I removed 2 of the the IFERROR() functions as it wasn't really necessary.

    My final validation formula:

    Please Login or Register  to view this content.
    Still, Excel should accommodate for longer validation formulas, as much as a cell entry.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,463

    Re: Substitute reference in cell formula with another reference.

    I guess your format is:
    "VB000000-0000xxx"
    with xxx is character, not value?

    If yes, there should be AND with 3 statements:

    EXACT(LEFT(A2,2),"VB")
    ISNUMBER(MID(A2,3,6)+MID(A2,10,4))
    NOT(ISNUMBER(RIGHT(A2,3)+0))

    combine together:

    =AND(EXACT(LEFT(A2,2),"VB"),ISNUMBER(MID(A2,3,6)+MID(A2,10,4)),NOT(ISNUMBER(RIGHT(A2,3)+0)))

    put it in to data validation, with Allow:Custom
    Attached Files Attached Files

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,463

    Re: Substitute reference in cell formula with another reference.

    Opp, I forgot the "-" in 9th position. Just add.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-16-2021
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Substitute reference in cell formula with another reference.

    Please Login or Register  to view this content.
    Will still evaluate to true if one or two out of the 3 characters are non-alphabets. They must evaluate to FALSE if one of them is a non-alphabet, so I'll stick to the old formula, but thanks for your suggestions, it's further shortened.
    Last edited by harpbelle; 04-17-2021 at 01:13 AM.

+ 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. Replies: 3
    Last Post: 03-22-2021, 10:21 AM
  2. [SOLVED] In this formula is there a way to substitute the pivot table reference with formula?
    By Vaslo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-27-2018, 12:01 PM
  3. [SOLVED] How to replace or substitute function formulas but left the cell reference intact?
    By Franky alta in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-02-2016, 12:48 AM
  4. [SOLVED] Can i substitute using a cell reference?
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-25-2013, 03:17 PM
  5. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  6. How to substitute dates using cell reference in SQL query using VBA
    By labman39 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-29-2012, 01:07 AM
  7. Replies: 11
    Last Post: 02-22-2006, 09:20 AM

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