EXCEL: List Cell Number if contains numberClosed

Question

Hello, 

I need help here.
Have tried everything I can think of and still not getting the results I need.

I need a formula that will list the name of a cell/cells if a certain number is present.
Example.... I6 contains the formula =IF(PRODUCT(D6:H6)>0,COUNT(D6:H6),"")
This way it counts up to 5 as data is entered into the corresponding cells.
I have this formula for Cells I6:I1201

I now need a formula that will list the name of the cell (ie, I6, I20, I1200 etc..) if/when that cell shows 5 from the formula I listed.

Also, some of these cells will be blank as I have gotten errors to that effect when I have tried some formulas.

ANY help is greatly appreciated.

Thanks

rizvisa1 · Answer

try this 

=IF(I6=5,ADDRESS(ROW(),9,4), "") 

just curious why your you want a formula to show you i6 ? 

why not simple 

=IF(i6=5, "I6", "")

db_excel · Answer

Thank you for the reply.
Unfortunately it listed the wrong cell (F1)

Let me try and explain this better.
I have a list (~1200 rows) of Data (A6:A1200)
For each of those rows of data, I place a date when it is accessed.
When those dates per row add up to 5 (a single row of data was accessed 5 times) it places a 5 in column I (I6:I1200).
What I need is when a row of data shows 5 times accessed, that it shows that row in a separate cell so I know which one needs to be addressed out of the ~1200.

Example:
A6, A9, A1100 were each accessed 5 times, so I6, I9 and I1100 each show the number 5.
So, I need a formula that will show I6, I9, I1100 in a separate cell up on top of the sheet so I know which out of the ~1200 that need to be addressed without scrolling line by line until I find them.

Hope this makes better sense.

TIA

rizvisa1 · Answer

So are you looking for one cell to give you a comma separated list of cell address ?

have you considered highlighting the cells in I column if the value is less than 5 ? This can be done wth conditional formatting and cell is immediately recognizable.

db_excel · Answer

Hello.
Yes, I currently have it to highlight that row when the corresponding row in Column I hits 5 (bright red so can see it easy).
I also have it list the MAX out of I6:I1200 in a separate column so when one of them hits 5, it lights up a big "5" in Red up top so I know I need to address at least one row.

Problem with that is I do not know which out of these ~1200 needs addressing and as the sheet grows larger it will be even more difficult to scan down every row until I find one and address it.
Plus, I have no indication if it is only 1 row of data that was accessed 5 times or 100 rows.

So, if I can have a cell up top that gives me the comma separated list of cell address that have hit 5 (out of column I) then I will know which to address, where and how many of them there are.

TIA!

rizvisa1 · Answer

I must be missing some thing here. Comma delimited is possible but you can run into cell text limitation (potentially), 

What I was suggesting was, the row on its own turn red, if I column is 5 and turns off the color if that is not that case, so you dont have to do any manually highlight the row.  

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site  , http://wikisend.com/  ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1  http://www.editgrid.com  etc and post back here the link

db_excel · Answer

Here is a sample of what I am working with.
I have only included a few lines of data.
Add in dates under the "PULL" columns until you hit pull 5 to get an idea of how it is set up now.

http://docs.google.com/fileview?id=0B9PkpeqwOG0LZDc1YmM0YTQtYmMwNS00ZjkzLWI4NGUtMzYyYjI1OThkYzE4&hl=en

D.

rizvisa1 · Answer

Says "Sorry, the page (or document) you have requested is not available."

db_excel · Answer

Click Download (22k) 

The preview is not available, but the document is.

rizvisa1 · Answer

K got it now

db_excel · Answer

Ok, fixed link (should work now)

rizvisa1 · Answer

Try this


Function FiveLocation(myRange As Range) As String
Dim lMaxRows As Long

    lMaxRows = 0
    On Error Resume Next
    lMaxRows = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error Resume Next
    
    
    For cellrow = 2 To lMaxRows
    
        If Cells(cellrow, "I") = 5 Then Add = Add & ", I" & cellrow
    Next
    
    If Left(Add, 1) = "," Then Add = Trim(Mid(Add, 2))
    FiveLocation = Add
    
End Function

To use it call like
=FiveLocation(i:i)

EXCEL: List Cell Number if contains number

11 responses

Similar discussions

Subscribe To Our Newsletter!