+ Reply to Thread
Results 1 to 16 of 16

please check, where is the problem so that not getting result in column D

  1. #1
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184

    please check, where is the problem so that not getting result in column D

    Hi All,

    We have uploaded a excel file, there are two sheets, sheet1 & sheet2,

    we have used a formula in sheet 1, column G (its o.k.), but when we try to add a new formula in sheet2, column d not getting the result (get 4 value from formula)

    we have tried it but not find the solution, in sample file, we have used the formula in sheet2 D5, pls drop down it to D16 and you find that its not working.

    Maybe formula in column D is unable to get the text "Flag" that's why not taking the 4 numbers through formula

    pls check and provide the solution.

    Thanks

    Forum link from where we have got the answer
    https://www.mrexcel.com/board/thread...rmula.1131247/
    Attached Files Attached Files
    Last edited by Ajay45822; 04-20-2020 at 05:59 AM. Reason: Got The Answers

  2. #2
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

    Re: error in formula, please check and update what is wrong

    Hi Ajay45822

    For me, your formulas in "D" call their own result with the column "G" which creates a circular reference and is therefore not calculable by Excel

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184
    Thanks bro for giving me time on my post

    Any other option, so may i get the solution


    Quote Originally Posted by BrianM45 View Post
    Hi Ajay45822

    For me, your formulas in "D" call their own result with the column "G" which creates a circular reference and is therefore not calculable by Excel

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: error in formula, please check and update what is wrong

    Please post an xls file with the wanted result manualy in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184

    Re: error in formula, please check and update what is wrong

    Thanks oeldere,

    please find attached the file.

    sheet1 is data, sheet2 is required result

    required process is if we will get the Flag in column in F, in column D we will take the three value from column E and start search again for Flag and when we find will take the 3 values from column E

    you can understand by it with our previous file.

    thanks for your attention
    Attached Files Attached Files

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,652

    Re: error in formula, please check and update what is wrong

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
    Last edited by Pepe Le Mokko; 04-19-2020 at 06:46 AM.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: error in formula, please check and update what is wrong

    After you change your title, please add an .xlsx file (I was not able to open it).

  8. #8
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184

    Re: error in formula, please check and update what is wrong

    ok, we are writing my query in detail and thank you all who have see my post and give me time

    we have two points, if any one solved, my problem will be solved

    Point1. What we are doing in this file – There are four entries in column D, we just want a formula who will search entry1 from next cell in column F and when find anything will show the “flag” and stop searching, when we find Flag we will put the new value in same cell column E where we have find “Flag” in column G
    We have got the below solution from a thread in this forum its working fine but problem is only that we need “Flag or any other Text value” but not by doing
    HTML Code: 
    [>0]"Flag";""
    we are attaching "file 1" where we have used a formula in "column F" which is

    HTML Code: 
    =IF(COUNTIF($G$4:G4,LOOKUP(2,1/SEARCH(F5,$E$4:E4),ROW($E$4:E4)))>0,0,IFERROR(LOOKUP(2,1/SEARCH(F5,$E$4:E4),ROW($E$4:E4)),""))
    and after that we have done - selected range G5:G16 and applied
    HTML Code: 
    [>0]"Flag";""
    number format.
    And get “Flag” but my problem is, Can we get "Flag" in column F without doing "selected range G5:G16 and applied
    HTML Code: 
    [>0]"Flag";""
    number format." If we can get my problem will be solved.

    or can u please provide a new code

    Point2. Please provide a new formula/code/vba/programme who can do all things done at once which we have asked in thread #1
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,871

    Re: error in formula, please check and update what is wrong

    After you change your title, answer awaits you.

  10. #10
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184

    Re: error in formula, please check and update what is wrong

    thanks oeldere ji,

    please find the file again

    and Earlier Formula used in D3

    =IF(OR($B53=3,$F53="Flag"),Get4Numbers($E$53:$E53),"") then copied down.

    UDF Get3Numbers is used.
    Code for UDF

    HTML Code: 
    Function Get3Numbers(Batch As Range)
    
    Dim T As Integer, X As Integer
    Get3Numbers = ","
    
    For T = 1 To WorksheetFunction.Min(3, Batch.Rows.Count)
      If IsNumeric(Batch.Cells(Batch.Rows.Count - X, 1)) Then
        
        If InStr(1, Get3Numbers, "," & Batch.Cells(Batch.Rows.Count - X, 1) & ",") = 0 Then
        Get3Numbers = "," & Batch.Cells(Batch.Rows.Count - X, 1) & Get3Numbers
        Else
        T = T - 1
        End If
      
      End If
    X = X + 1
    
    Next T
    
    If Get3Numbers <> "" Then Get3Numbers = Mid(Left(Get3Numbers, Len(Get3Numbers) - 1), 2)
    
    End Function

  11. #11
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,652

    Re: error in formula, please check and update what is wrong

    Please read post #6., edit your first post and change your title to something descriptive of your Excel problem.
    Thanks

  12. #12
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184

    Re: error in formula, please check and update what is wrong

    Dear Pepe Le Mokko,

    we have given the reply on #8, also change the title

    and also sent u a message, please check and help

  13. #13
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,652

    Re: please check, where is the problem so that not getting result in column D

    If anybody out there understands what the OP is trying to achieve ( I don't understand) any suggestion for a decent title is welcome

    As I do not understand what the OP is trying to do, help can be provided as usual
    Last edited by Pepe Le Mokko; 04-19-2020 at 06:49 AM.

  14. #14
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    184

    Re: please check, where is the problem so that not getting result in column D

    Got the solution in other Forum,

    Thanks for your forum support also

  15. #15
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

    Re: please check, where is the problem so that not getting result in column D

    Hi Ajay45822

    Even if I am not an administrator, it would be good to put the solution found here.
    This would allow those who will fall on this subject, to know it

    Thanks

  16. #16
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,652

    Re: please check, where is the problem so that not getting result in column D

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

    AKA VISHAAL
    Last edited by Pepe Le Mokko; 04-20-2020 at 02:36 AM.

+ 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. [SOLVED] #NAME? error but there is nothing wrong with my formula
    By jordan.y in forum Excel General
    Replies: 5
    Last Post: 09-11-2019, 05:04 PM
  2. [SOLVED] What Is Wrong With This TIMEVALUE Formula ? Gives #VALUE! error
    By Logit in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-24-2017, 12:38 AM
  3. [SOLVED] Error in Vlookup string: A value used in the formula is of the wrong data type.
    By Taylorim in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2016, 08:20 AM
  4. [SOLVED] Index formula #Num! error and cell displays wrong result
    By eulerlagrange in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2016, 04:14 PM
  5. [SOLVED] =COUNTIF() - Check my formula - doing something wrong?
    By domgilberto in forum Excel General
    Replies: 35
    Last Post: 05-18-2014, 10:32 AM
  6. Replies: 4
    Last Post: 04-20-2012, 08:44 AM
  7. #VALUE! Error What wrong with formula
    By JohnDear in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2011, 01:26 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