Results 1 to 5 of 5

How to create a sentence from variables in different cells - out of my depth!!

Threaded View

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    2

    Lightbulb How to create a sentence from variables in different cells - out of my depth!!

    Hi all - if possible, could you please take a second to help me?! I'm now well out of my depth!

    I'm trying to create a roster template that automatically generates a sentence which takes information from a selection of cells BUT omits certain phrases and includes variable punctuality to create a proper sentence. I have attached (i think...!) my roster in it's current state where i have been able to generate a list separated by commas, but have a couple of hurdles i cannot overcome alone! a LOT of trial and error to get the current formula working but have encountered some issues...

    At present my formula takes the day (i.e. Mon) from one cell, the shift (i.e. 6am) from another cell and presents as "Mon 6am, Tues 6am" etc. I have created the formula so that a full stop appears after the Saturday shift - however, there will not always be a saturday shift which opened a whole new can of worms for me - is it possible to create a formula that will change the punctuation depending on the variables i.e., shift? For example, i would LOVE it to take a Mon, Tues, Wed, Fri roster and display as: Mon 6am, Tues 6am, Wed 6am & Fri 6am.

    Obviously there would have to be a rule that recognised how many days/shifts there were and allocated the correct punctuation. I have googled the heck out of it but turned up nothing. Not sure if this is even possible.

    Secondly, i need to display the following terms in the matrix (notes for me about a certain person's availability for work on a certain day) BUT with my current formula these terms are carried across into the roster sentence (which i will be smsing to my staff) - and i dont want them too. For example, if i say "day only" on Monday it carries across to the roster as "Mon day only, Tues 6am" etc

    Sorry for the lengthy post - just wanting to get as much explanation of what i'd like down as possible to see if anyone can help me at all! Thanks for taking the time to read...!

    To summarise i need my formula (I have included this in the attachment) to do the following:
    [*] create a list of variable days/shift times: DAY <space> TIME <comma> etc...
    [*] omit the following terms: "NA, day only, arvo only, night only, day/arvo, arvo/night" from the roster sentence
    [*] create a list with variable punctuality (i.e., commas between each day/shift and then the last shift should be: "&" <space> DAY <space> TIME <fullstop>

    Can you help?? Thank you
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Replace cells and create dummy variables
    By slacknoise in forum Excel General
    Replies: 7
    Last Post: 06-18-2012, 02:40 PM
  2. [SOLVED] Create Sentence using other cells
    By blacky1 in forum Excel General
    Replies: 3
    Last Post: 04-20-2012, 10:01 PM
  3. Excel 2007 : create a sentence from 4 cells
    By NavNeet2912 in forum Excel General
    Replies: 2
    Last Post: 04-22-2011, 11:03 AM
  4. [SOLVED] How do i add a cell value in a sentence without using 2 cells?
    By Jared in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2006, 11:15 AM
  5. Combining text and cell value to create a sentence
    By StephanieH in forum Excel General
    Replies: 4
    Last Post: 06-13-2005, 09:05 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