+ Reply to Thread
Results 1 to 7 of 7

associate text with date

  1. 11-26-2013, 05:43 PM #1
    brendanmarie
    brendanmarie is offline
    Registered User
    Join Date
    11-26-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2003
    Posts
    4

    associate text with date

    Hello. I have dates in column A and names in column B (each row is a date and a corresponding name). I want to be able to enter one of those names in a cell on a separate sheet to see if today's date is 7 days or greater from the date next to that name on the original sheet. To explain the context, I'm signing people in for a service on the original spreadsheet, putting the date of the service in column A and their name in column B. They are allowed to come in for the service a maximum of once every 7 days, so I want to be able to type their name into a given cell and see if it has been at least 7 days since their last visit. Any help would be greatly appreciated.
    Reply With QuoteRegister To Reply
  2. 11-26-2013, 05:50 PM #2
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: associate text with date

    Maybe something like this:
    In Sheet2 a1 : name
    b1:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post
    Reply With QuoteRegister To Reply
  3. 11-26-2013, 05:56 PM #3
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: associate text with date

    Where D2 = a name you type in

    =IF(TODAY()-LOOKUP(2,1/($B$1:$B$600=D2),$A$1:$A$600)>=7,"Ok","Too Soon")
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!
    Reply With QuoteRegister To Reply
  4. 11-26-2013, 06:12 PM #4
    brendanmarie
    brendanmarie is offline
    Registered User
    Join Date
    11-26-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: associate text with date

    Thank you both so much! Daffodil, your formula worked perfectly for me.
    Reply With QuoteRegister To Reply
  5. 11-26-2013, 06:24 PM #5
    brendanmarie
    brendanmarie is offline
    Registered User
    Join Date
    11-26-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: associate text with date

    Oops! Well, it works for calculating it in the same sheet, but how do I do it from a separate sheet?
    Reply With QuoteRegister To Reply
  6. 11-26-2013, 06:30 PM #6
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: associate text with date

    You just change the references by declaring the sheet name followed by an exclamation point.

    For example: =SUM(Sheet1!A1:A10)

    So, if your first tab is UnicornPower and you're checking names in D2 on KittenExplosion, your formula might instead look like:

    =IF(TODAY()-LOOKUP(2,1/(UnicornPower!$B$1:$B$600=KittenExplosion!D2),UnicornPower!$A$1:$A$600)>=7,"Ok","Too Soon")


    Edit: If there's a space in the tab name, you'll need to apostrophize the whole string.

    Example: =SUM('I named this tab something really long'!A1:A10)




    Apostrophize is probably a word.
    Last edited by daffodil11; 11-26-2013 at 06:34 PM. Reason: Apostrophization!
    Reply With QuoteRegister To Reply
  7. 11-26-2013, 06:44 PM #7
    brendanmarie
    brendanmarie is offline
    Registered User
    Join Date
    11-26-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: associate text with date

    Great! Thank you again!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Text to Row instead of to column comma separated and associate related cells
    By oahmadi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-16-2013, 03:51 PM
  2. [SOLVED] vba code or formula assistance to associate a date, text, and value to a summary report.
    By lilsnoop in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-01-2013, 10:52 AM
  3. How to associate multiple cells associated with one cell until a different date is entered
    By jp39208 in forum Excel General
    Replies: 2
    Last Post: 05-18-2012, 09:27 AM
  4. Associate Cells
    By djedjridoo in forum Excel General
    Replies: 1
    Last Post: 11-26-2011, 02:47 PM
  5. Associate a word with a value?
    By Weasel in forum Excel General
    Replies: 1
    Last Post: 01-09-2007, 12:08 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1