Formatting Problem in Formula

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

We have used a formula in column G, but when we check the Cell Format of any E5:F16 its coming

Untitled.png


but when we check the cell format of G5:G16 it is coming the following

Untitled2.png


Problem is how can we get the Cell Format setting as per first image in column G

please find the excel file also.

xlsx file as u have told.xlsx
EFG
4AmountBatchTesting
53000 
63200 
73000,3200,85008500 
84000 
92000 
104000,2000,30003000Flag
117654 
123000,8000,40004000Flag
136000 
144000,6000,30003000Flag
155000 
166000Flag
Sheet1
Cell Formulas
RangeFormula
G5:G16G5=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)),""))
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi there, could you explain what your formula is trying to achieve?
 
Upvote 0
Yes its trying to find the E7 three value in column F but from the F8 (any one amount) and after that it will stop working and again we will put the new value in column E and it will start the doing same thing again
 
Upvote 0
Do you mean that rather than getting the word "flag" in col G, you want to see the number from the formula?
 
Upvote 0
thanks Fluff ji,

may be it will work in my case,

by the way my main problem is -
we are getting the Flag but when we try to put any formula in Column E10 its not taking is a Flag, you can try the following code in E10
VBA Code:
=IF(OR($B10=3,$G10="Flag"),Get4Numbers($F$6:$F10),"")



Do you mean that rather than getting the word "flag" in col G, you want to see the number from the formula?
 
Upvote 0
I can't try that formula, because it uses a UDF, which I don't have.
 
Upvote 0
Pls find the UDF

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

I can't try that formula, because it uses a UDF, which I don't have.
 
Upvote 0
The reason it doesn't work is that column G does not contain the word "Flag" it contains either "" or a number
 
Upvote 0
You are right, what can we do that in that case, we need any text

The reason it doesn't work is that column G does not contain the word "Flag" it contains either "" or a number
 
Upvote 0
Maybe
=IF(OR($B10=3,$G10<>""),Get4Numbers($F$6:$F10),"")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top