+ Reply to Thread
Results 1 to 13 of 13

formula works on one sheet but not the other. Non-blank max number

  1. #1
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    formula works on one sheet but not the other. Non-blank max number

    I'm trying to calculate the max number between 2 non blank cells in column A, using the values in column B when B>C. There are no blanks in B:B. C is always blank unless C>B.

    IF(A2<>"";MAX(OFFSET(B2:C2;0;0;IFERROR(MATCH("*";OFFSET(A3;0;0;COUNTA(B:B)-ROW()+1);0);COUNTA(B:B)-ROW()+1)));"")

    The above formula works on one sheet where I experiment with the calculation, but now that it works there, it doesn't on the real sheet I'm working on.

    On the real worksheet, the formula is:

    IF(A2<>"";MAX(OFFSET(H2:I2;0;0;IFERROR(MATCH("*";OFFSET(A3;0;0;COUNTA(H:H)-ROW()+1);0);COUNTA(H:H)-ROW()+1)));"")

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: formula works on one sheet but not the other. Non-blank max number

    Use the formula in the same row.

    Because Row() will result the formula cell Row() number, so it may be one of the cause for not getting the desired result


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    Re: formula works on one sheet but not the other. Non-blank max number

    What do you mean by using it on the same row? As opposed to what?

  4. #4
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    Re: formula works on one sheet but not the other. Non-blank max number

    Quote Originally Posted by :) Sixthsense :) View Post
    Use the formula in the same row.

    Because Row() will result the formula cell Row() number, so it may be one of the cause for not getting the desired result
    What do you mean by using it on the same row? As opposed to what?

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: formula works on one sheet but not the other. Non-blank max number

    If that formula is originally used on 3rd row then in your worksheet you have to use it on 3rd row only. Because Row() will result current row number.

  6. #6
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    Re: formula works on one sheet but not the other. Non-blank max number

    Quote Originally Posted by :) Sixthsense :) View Post
    If that formula is originally used on 3rd row then in your worksheet you have to use it on 3rd row only. Because Row() will result current row number.
    I don't think that is the problem. The formula works fine on Sheet 2 but not when translating it to Sheet 1.
    Can you spot what the issue might be?
    Attached Files Attached Files

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: formula works on one sheet but not the other. Non-blank max number

    Can you please give me a brief about your data and what you are trying to do?

  8. #8
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    Re: formula works on one sheet but not the other. Non-blank max number

    Quote Originally Posted by :) Sixthsense :) View Post
    Can you please give me a brief about your data and what you are trying to do?
    The relevant columns are the ones that have a title (the rest have already been formatted on the original excel with 1100 rows)
    I'm trying to calculate what the new order point (Nuevo PP) of an item should be. This is equal to the quantity (CANT) unless there is a value in MAX CANT. In such case, it should equal this value. Each item is determined by a code, hence why it needs to bare in mind the blanks in column A; all order quantities belong to a code until a new one is plotted in A.
    On sheet 2, the formula works well; if MAX CANT>CANT it bears in mind this value (if CANT>MAX CANT it uses the value in CANT). However, this doesn't seem to work on Sheet 1.

  9. #9
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: formula works on one sheet but not the other. Non-blank max number

    Double jumped the gun, I was initially right, but was mentioning the incorrect cell.

    The MATCH function doesn't work in Sheet1 because the MATCH function which tries to find "*" under CODIGO (column A) is catching what looks seemingly like blank cells (A3:A6). If you test these cells =ISBLANK(A3), they aren't actually blank.

    Having said that, I've not thought of a work-around other than to manually go into these cells and "delete" them.
    Last edited by quekbc; 07-08-2015 at 08:08 AM.

  10. #10
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    Re: formula works on one sheet but not the other. Non-blank max number

    What do you mean?
    Were you able to follow what I intend to do?

  11. #11
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: formula works on one sheet but not the other. Non-blank max number

    Yes, I think. Say, in cell E2, you want it to be MAX(H2:I6), which will be 2. However, the formula in E2 is currently 1.

    The issue I found is, if you select cell E3:E6, and hit the Delete key, voila, the formula suddenly works. Therein lies the problem. Those "blank" cells are really blank

  12. #12
    Registered User
    Join Date
    07-08-2015
    Location
    Spain
    MS-Off Ver
    2010
    Posts
    25

    Re: formula works on one sheet but not the other. Non-blank max number

    Quote Originally Posted by quekbc View Post

    Having said that, I've not thought of a work-around other than to manually go into these cells and "delete" them.
    Thank you so much!! In the complete file there are 1100 rows so this would take too long. However, I've figured you can do the following (for your future use): highlight the selection; ctrl+f; replac; find (leave it in blank) and replace with 0; match case + replace all; then find 0 and replace by nothing :D

    I'm going to try it out now on the real sheet!!

  13. #13
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: formula works on one sheet but not the other. Non-blank max number

    Excellent. Credit to SixthSense. I'm just a troubleshooter.

+ 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: 5
    Last Post: 08-15-2014, 08:33 AM
  2. [SOLVED] Formula works on one sheet, but not for other sheet.
    By Keila in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-21-2012, 04:08 PM
  3. formula works in one sheet but not another
    By sdts in forum Excel General
    Replies: 2
    Last Post: 02-03-2012, 07:11 AM
  4. If number (Combo! Formula) If blank Cell Blank...
    By Zees in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 09-27-2010, 01:15 PM
  5. Formula to copy data to new sheet works but gives an error when run
    By bm1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2010, 03:34 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