Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
showing cell locations per answer from Ron Coddere - stuck!
I am trying to track 3 things, with the info from a linked file.
1. If the "name" I'm looking for is in the reference file. I'm using =IF(COUNTIF('2009_Maps(1).xls'!southbed,B2)0,"sou th", " ") 2.The number of occurences of the name. I'm using =COUNTIF('2009_Maps(1).xls'!southbed,B2) Both these formula's work, provided that the name matches exactly. For example, this will find "cadmium" but not "cadmium sp". How can I check for cells that contain the partial name, as well as the full name? 3. I want to show the cell address for each occurence. I can across a post from Ron that gave me this formula to find a cell location. =ADDRESS(SUMPRODUCT((A1:E10=G1)*ROW(A1:A10)),SUMPR ODUCT((A1:E10=G1)*COLUMN(A1:E1))) I have am using a range, and want nothing in the field if there are no occurences, so I have used this. =IFERROR(ADDRESS(SUMPRODUCT(('2009_Maps(1).xls'!so uthbed=B4)*ROW('2009_Maps(1).xls'!southbed)),SUMPR ODUCT(('2009_Maps(1).xls'!southbed=B4)*COLUMN('200 9_Maps(1).xls'!southbed)))," ") However, this only works when there is one location. How can I have cell address' for ALL locations show up. And have the answer, $CZ$613, show as CZ613. I'm in over my head, and the head of my resident Excel guru!, so I'm hoping someone can help |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix my spreadsheet from showing formula to showing answer | Excel Discussion (Misc queries) | |||
How to fix my spreadsheet from showing formula to showing answer | Excel Discussion (Misc queries) | |||
An easy answer I'm sure, so why am I stuck? | Excel Discussion (Misc queries) | |||
Excel:Swap cell contents-including cell groups-betwen 2 locations | Excel Discussion (Misc queries) | |||
Cell locations | Excel Discussion (Misc queries) |