# EXCEL: List Cell Number if contains number

Closed
db_excel - Jun 9, 2010 at 01:23 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 11, 2010 at 12:20 PM
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
Related:

## 11 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 03:54 PM
try this

just curious why your you want a formula to show you i6 ?

why not simple

=IF(i6=5, "I6", "")
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 04:12 PM
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!

Didn't find the answer you are looking for?

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 04:34 PM
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.

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.

D.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 05:07 PM
Says "Sorry, the page (or document) you have requested is not available."

The preview is not available, but the document is.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 05:19 PM
K got it now
Ok, fixed link (should work now)
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 05:40 PM
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

End Function```

To use it call like
=FiveLocation(i:i)