+ Reply to Thread
Results 1 to 6 of 6

Format cells based on 2 separate cell data, incorporated into the format

  1. #1
    Registered User
    Join Date
    04-17-2019
    Location
    Alberta, Canada
    MS-Off Ver
    Office 365
    Posts
    11

    Format cells based on 2 separate cell data, incorporated into the format

    My Title makes no sense, i know.

    I am trying to generate a reference number.
    The reference number is generated based on a few factors..
    In this case the reference number ALWAYS starts out the same
    REF#06-RT1000
    It is the next series of numbers that is extremely important and are based off of data (the price) entered into another cell.
    This is easy, as I can format my column to auto populate my reference number and use a formula to add the price in at the end so that if the price were "6.99" my reference number would look like:
    REF#06-RT1000699
    HOWEVER, sometimes we have special pricing and I need to incorporate the 2 amounts...
    I am struggling to find a way to incorporate that into my format & formula
    So if the price is 6.99 but i have special pricing of 4.99 my reference number needs to look like:
    REF#06-RT1000699SPC499

    so I have seperate cells with the different prices but it becomes difficult and poses threat for error if I need to manually add this soecial pricing to the end of my refernece number./

    I have attached a spreadsheet to show what i mean.

    THanks.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,156

    Re: Format cells based on 2 separate cell data, incorporated into the format

    I have made a few assumptions:
    • Cell H2 is used as an example
    • The special pricing is in N2
    • If there is no special pricing then N2 is blank.

    With these assumptions the formula in H2 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The result in H2 is: REF#06-RT1000307175

    Let me know whether this is along the lines of what you are looking for.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  3. #3
    Registered User
    Join Date
    04-17-2019
    Location
    Alberta, Canada
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Format cells based on 2 separate cell data, incorporated into the format

    This is pretty much what I am looking for, and your assumptions are accurate, however, is there a way to differentiate between the 2 prices? Typically my reference numbers, when there is special pricing, look like this: REF#06-RT100307SP175. The SP tells my sales department that there is Special Pricing available on that product. If it is not possible to incorporate the "SP" into the reference number, is there another option? Maybe "." or ":".. just something to separate the 2 numbers.
    Thank you so much!

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,156

    Re: Format cells based on 2 separate cell data, incorporated into the format

    Sorry, I overlooked the "SP" separator. Please try the following in H2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It's a bit less straightforward than ideal because 307SP200 is no longer numeric and so the custom format that you are using to generate the "REF#06-RT1000" prefix no longer works and so I needed to add this prefix explicitly into the formula

    Let me know if this works for you

  5. #5
    Registered User
    Join Date
    04-17-2019
    Location
    Alberta, Canada
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Format cells based on 2 separate cell data, incorporated into the format

    thank you this was most helpful

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: Format cells based on 2 separate cell data, incorporated into the format

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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] Change automatically Data Label & Vertical Axis format based on data value format
    By Neilesh Kumar in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-01-2018, 01:14 AM
  2. Changing Cell format based on another cells format.
    By MrStevie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2017, 03:02 PM
  3. Replies: 0
    Last Post: 10-02-2016, 07:30 AM
  4. [SOLVED] Macro to split cell data across multiple cells based on format-
    By Biased Historian in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2013, 12:57 PM
  5. Replies: 2
    Last Post: 09-26-2012, 04:43 AM
  6. Format cell based on value(s) in separate column
    By WillDLC in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-02-2011, 03:29 PM
  7. [SOLVED] how do i format a cell based on format of a range of cells?
    By Chris Hardick in forum Excel General
    Replies: 2
    Last Post: 04-03-2006, 03:55 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