EXCEL: List Cell Number if contains number [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

11 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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", "")
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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.
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!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Says "Sorry, the page (or document) you have requested is not available."
Click Download (22k)

The preview is not available, but the document is.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
K got it now
Ok, fixed link (should work now)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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)
Sorry for the late reply.
Was trying like mad yesterday to get this to work and keep getting errors.
I am sure I am doing something wrong when I type it in, just not sure what.

Any suggestions?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Could you post a workbook with the code and data and tell me what steps i need to perform to get the error
Hmm, looks like Office 2008 (mac) which I use at work may be the culprit.
Seems VBA is disabled (not included) in this version.
Am I right/wrong??
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
I dont have mac or 2008 but I highly doubt that macro is not available. By default they might be disabled. See help on macro, may be they would have some thing there

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!