+ Reply to Thread
Results 1 to 16 of 16

Formula only works on first cell specified in range

  1. #1
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Question Formula only works on first cell specified in range

    Hello

    I am making a calendar for my band, and i have an overview page then a page for each month. What i want to happen is when i enter something on the detailed page it changes the fill of the according day on the overview. With the formula's i am using it is only working for the first cell and not the rest specified in the range.

    Here are my formulas

    =ISTEXT(January!B5:B52) (grey fill) applies to =$C$2
    =NOTISTEXT(January!B5:B52) (no fill) applies to =$C$2


    2018-03-20 (1).png

    2018-03-20.png

    *Edit*
    Working on first cell

    2018-03-20 (2).png

    2018-03-20 (3).png

    Not working on other cells

    2018-03-20 (4).png

    (see comments for other photo)

    Any ideas ?

    Thanks

    Bensley
    Last edited by Bensley; 03-22-2018 at 06:28 AM.

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Formula only works on first cell specified in range

    Can you attach a sample workbook with what you expect to be displayed?

  3. #3
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    Other photo

    Attachment 566434

  4. #4
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    I have updated it, there is a photo in the comment above as i can only upload 5 in the post

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Formula only works on first cell specified in range

    Attaching a workbook would be much more helpful. My eyes aren't what they used to be.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Formula only works on first cell specified in range

    maybe try =ISNONTEXT(value)

    something like (except UDF) NOTISTEXT doesn't exist or you forgot brackets: NOT(ISTEXT(value))
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    Quote Originally Posted by kersplash View Post
    Attaching a workbook would be much more helpful. My eyes aren't what they used to be.


    I can't seem to find the attach document??

  8. #8
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    Quote Originally Posted by sandy666 View Post
    maybe try =ISNONTEXT(value)

    something like (except UDF) NOTISTEXT doesn't exist or you forgot brackets: NOT(ISTEXT(value))
    The NOTISTEXT is just there to change it back to clear if we remove what was written, it is working on first cell only as well

  9. #9
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Formula only works on first cell specified in range

    To attach - click Go Advanced then Manage Attachments then Upload.

  10. #10
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    Here you go
    Attached Files Attached Files

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Formula only works on first cell specified in range

    Quote Originally Posted by Bensley View Post
    The NOTISTEXT is just there to change it back to clear if we remove what was written, it is working on first cell only as well
    It's not necessary because if in cell will be nothing CF return value 0 so it means doesn't change color.
    CF works with TRUE or FALSE (1 or 0) and nothing more

  12. #12
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Formula only works on first cell specified in range

    Try this as the only rule to replace the ones you have;

    Capture.JPG

  13. #13
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    Quote Originally Posted by sandy666 View Post
    It's not necessary because if in cell will be nothing CF return value 0 so it means doesn't change color.
    CF works with TRUE or FALSE (1 or 0) and nothing more
    Yes but it meant that if i cancelled or moved something i had to go change the colour back to blue or what ever manually, when you deleted text it would just unlock the colour allowing me to change it again, using the not, made it change it back

  14. #14
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    Quote Originally Posted by kersplash View Post
    Try this as the only rule to replace the ones you have;

    Attachment 566438
    That worked perfectly

    Do you know of any short cuts (i know excel is meant to be fairly good at learning) that would make applying that formula to each day quicker, or is it just slog through it

  15. #15
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Formula only works on first cell specified in range

    I don't know, but I hope for your sake, since you have to have a rule for every single day.

  16. #16
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Formula only works on first cell specified in range

    So I worked out how to get it to copy across so that i just have to change it for each month (as i am referencing a different page) using

    Formula
    Please Login or Register  to view this content.
    (Font)
    Applies to
    Please Login or Register  to view this content.


    But now i have my original problem, i have all days highlighting in the month that i have done (January) but if i choose any time slot other then 00:00 it won't register, what have i done wrong ?
    Attached Files Attached Files

+ 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: 11
    Last Post: 08-31-2010, 01:30 PM
  2. Replies: 2
    Last Post: 05-21-2009, 01:54 PM
  3. Offset Function works in cell, not in named range
    By DragonslayerApps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 AM
  4. [SOLVED] Offset Function works in cell, not in named range
    By DragonslayerApps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  5. Offset Function works in cell, not in named range
    By DragonslayerApps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. [SOLVED] Offset Function works in cell, not in named range
    By DragonslayerApps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. Offset Function works in cell, not in named range
    By DragonslayerApps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. [SOLVED] Offset Function works in cell, not in named range
    By DragonslayerApps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-25-2005, 12: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