+ Reply to Thread
Results 1 to 7 of 7

if cell more than 90 then show in another sheet.

  1. 07-09-2018, 12:49 PM #1
    anilpatni1234
    anilpatni1234 is offline
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    if cell more than 90 then show in another sheet.

    Hey Guys,

    I need a small help again.

    Sheet " In Hand" shows me the items which are with me in stock.
    Sheet " In Hand" range "L" shows me the number of days the item is kept with me.


    Now ,

    I want to view the items which are kept with me for more than 90 days in Sheet "QUARTER"

    I have worked out on a formulae which is 
    Please Login or Register  to view this content.
    on sheet "QUARTER" cell "A1"


    Is there any other way to do this?

    Because I want to view it without the blanks in between.

    It makes analyzing much better.

    I have attached my workbook.

    Please help me
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-09-2018, 01:00 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,493

    Re: if cell more than 90 then show in another sheet.

    It's the same principle as the other formula I gave you:

    =IFERROR(INDEX('IN HAND'!A:A,SMALL(IF('IN HAND'!$L$2:$L$20>90,ROW($A$2:$A$20)),ROWS($A$1:A1))),"")

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-09-2018, 01:15 PM #3
    anilpatni1234
    anilpatni1234 is offline
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: if cell more than 90 then show in another sheet.

    Hey Ali,

    One small doubt.

    how do i make it greater than 90 but less than 180 in the above formulae?

    is it >90<180 ?
    Reply With QuoteRegister To Reply
  4. 07-09-2018, 01:19 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,493

    Re: if cell more than 90 then show in another sheet.

    Like this:

    =IFERROR(INDEX('IN HAND'!A:A,SMALL(IF('IN HAND'!$L$2:$L$20>90,IF('IN HAND'!$L$2:$L$20<180,ROW($A$2:$A$20))),ROWS($A$1:A1))),"")
    Reply With QuoteRegister To Reply
  5. 07-09-2018, 01:23 PM #5
    anilpatni1234
    anilpatni1234 is offline
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: if cell more than 90 then show in another sheet.

    Hey Ali,

    Thank you so much.

    I have learnt a lot from you.

    Thank you so much
    Reply With QuoteRegister To Reply
  6. 07-09-2018, 01:24 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,493

    Re: if cell more than 90 then show in another sheet.

    You're welcome!

    I suggest you have a play and try to make it do a few more things for you - it's quite flexible, this formula.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  7. 07-09-2018, 01:27 PM #7
    anilpatni1234
    anilpatni1234 is offline
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: if cell more than 90 then show in another sheet.

    Thank you.

    I have marked it as SOLVED
    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. [SOLVED] Make Cell Show Certain Pay Rate Based on Cell in Another Sheet
    By jbransden in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2017, 06:23 AM
  2. Show Sheet Name of a Cell Referenced from Another Sheet
    By rcm242 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2017, 12:49 PM
  3. Can you show changes reflected in a different cell or sheet?
    By Meggo12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2016, 10:34 AM
  4. Can you show changes reflected in a different cell or sheet?
    By Meggo12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-13-2016, 09:31 AM
  5. Show sheet based on cell value
    By tigergutt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2015, 10:23 AM
  6. [SOLVED] If cell contains a certain word, show the corresponding sheet.
    By mbdigital in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-10-2014, 05:45 AM
  7. Show row of data on seperate sheet if certain cell is checked on original sheet
    By NeilM in forum Excel General
    Replies: 3
    Last Post: 05-17-2012, 08:53 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