Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: showing cell address' per ans from Ron Coddere - still st
address'Still trying to solve a problem with showing cell address'. I can
determine if the "name" if there, and how many occurences there are (Thanks Pete_UK), but I need to show the cell address for EACH occurence and that's where it goes west. I came across a post from Ron that gave me this formula to return a cell address. I am using a range, and want something in the field only if there is an occurence, so I have used this. =IFERROR(ADDRESS(SUMPRODUCT(('working with files\southbed=$B4)*ROW('working with files\southbed)),SUMPRODUCT(('working with files\southbed=$B4)*COLUMN('working with files\southbed)))," ") However, this only works when there is one location. For multiple locations, I get one result and it's way off. How can I show cell address for all occurences? I'd also like the cell address to show as CZ613, rather than $CZ$613. Any way to do this? Thanks, Mea |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: showing cell address' per ans from Ron Coddere - stillst
Hi Mea,
I didn't quite understand it first time around (and here's the link to your earlier post for anyone who's wondering: http://groups.google.com/group/micro...d3011ba5fb6?q= ), and I'm still not sure now. Do you think you could sketch out what you data looks like with a few examples, so that I can visualise it a bit better? Why do you need the cell addresses anyway? Pete On Aug 14, 7:19*pm, Mea wrote: address'Still trying to solve a problem with showing cell address'. I can determine if the "name" if there, and how many occurences there are (Thanks Pete_UK), but I need to show the cell address for EACH occurence and that's where it goes west. *I came across a post from Ron that gave me this formula to return a cell address. I am using a range, and want something in the field only if there is an occurence, so I have used this. =IFERROR(ADDRESS(SUMPRODUCT(('working with files\southbed=$B4)*ROW('working with files\southbed)),SUMPRODUCT(('working with files\southbed=$B4)*COLUMN('working with files\southbed)))," ") However, this only works when there is one location. For multiple locations, *I get one result and it's way off. How can I show cell address for all occurences? I'd also like the cell address to show as CZ613, rather than $CZ$613. Any way to do this? Thanks, Mea |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: showing cell address' per ans from Ron Coddere - still st
Is "southbed" a one dimensional array?
How many rows is "southbed" ? I'd also like the cell address to show as CZ613, rather than $CZ$613. You can get rid of the dollar signs by using the 3rd argument of ADDRESS and setting it to 4: =ADDRESS(some_row,some_column,4) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Hi Mea, I didn't quite understand it first time around (and here's the link to your earlier post for anyone who's wondering: http://groups.google.com/group/micro...d3011ba5fb6?q= ), and I'm still not sure now. Do you think you could sketch out what you data looks like with a few examples, so that I can visualise it a bit better? Why do you need the cell addresses anyway? Pete On Aug 14, 7:19 pm, Mea wrote: address'Still trying to solve a problem with showing cell address'. I can determine if the "name" if there, and how many occurences there are (Thanks Pete_UK), but I need to show the cell address for EACH occurence and that's where it goes west. I came across a post from Ron that gave me this formula to return a cell address. I am using a range, and want something in the field only if there is an occurence, so I have used this. =IFERROR(ADDRESS(SUMPRODUCT(('working with files\southbed=$B4)*ROW('working with files\southbed)),SUMPRODUCT(('working with files\southbed=$B4)*COLUMN('working with files\southbed)))," ") However, this only works when there is one location. For multiple locations, I get one result and it's way off. How can I show cell address for all occurences? I'd also like the cell address to show as CZ613, rather than $CZ$613. Any way to do this? Thanks, Mea |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: showing cell address' per ans from Ron Coddere - stil
phase quan quan no 0 cell add phase 1 quan quan no 0 cell add
steel phase 1 1 1 A4 phase 2 3 3 G17 copper phase 1 2 2 H9 0 cadmium phase 1 5 5 Y16 phase 2 3 3 G7 nickel phase 1 4 4 AF7 phase 2 3 3 Q4 This is the result I want to get. I am using: phase - IF(COUNTIF(map.xls!phase1,$A8)0,"phase 1", " ") quan - COUNTIF(map.xls!phase1,$A9) can't figure out how to make this show blank when the result is 0, so quan without showing a 0 - IF(C8=0,"",C8) cell address - IFERROR(ADDRESS(SUMPRODUCT((map.xls!phase1=$A8)*RO W(map.xls!phase1)),SUMPRODUCT((map.xls!phase1=$A8) *COLUMN(map.xls!phase1)),4)," As you can see, I get only 1 answer when there are 2+ showing in quantity The information is scattered throughout some other worksheets, so the cell address is most helpful. for example: oak nickel nickel cadmium cadmium birch birch cadmium nickel birch birch oak oak nickel cadmium steel larch copper birch copper larch birch oak larch birch oak larch cadmium birch maple larch Is there a way to attach an excel file? Thanks! "Pete_UK" wrote: Hi Mea, I didn't quite understand it first time around (and here's the link to your earlier post for anyone who's wondering: http://groups.google.com/group/micro...d3011ba5fb6?q= ), and I'm still not sure now. Do you think you could sketch out what you data looks like with a few examples, so that I can visualise it a bit better? Why do you need the cell addresses anyway? Pete On Aug 14, 7:19 pm, Mea wrote: address'Still trying to solve a problem with showing cell address'. I can determine if the "name" if there, and how many occurences there are (Thanks Pete_UK), but I need to show the cell address for EACH occurence and that's where it goes west. I came across a post from Ron that gave me this formula to return a cell address. I am using a range, and want something in the field only if there is an occurence, so I have used this. =IFERROR(ADDRESS(SUMPRODUCT(('working with files\southbed=$B4)*ROW('working with files\southbed)),SUMPRODUCT(('working with files\southbed=$B4)*COLUMN('working with files\southbed)))," ") However, this only works when there is one location. For multiple locations, I get one result and it's way off. How can I show cell address for all occurences? I'd also like the cell address to show as CZ613, rather than $CZ$613. Any way to do this? Thanks, Mea |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: showing cell address' per ans from Ron Coddere - stil
I've just attached a better example as a reply to Pete above.
Thanks for the "4"! Works great! Thanks for the "T. Valko" wrote: Is "southbed" a one dimensional array? How many rows is "southbed" ? I'd also like the cell address to show as CZ613, rather than $CZ$613. You can get rid of the dollar signs by using the 3rd argument of ADDRESS and setting it to 4: =ADDRESS(some_row,some_column,4) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Hi Mea, I didn't quite understand it first time around (and here's the link to your earlier post for anyone who's wondering: http://groups.google.com/group/micro...d3011ba5fb6?q= ), and I'm still not sure now. Do you think you could sketch out what you data looks like with a few examples, so that I can visualise it a bit better? Why do you need the cell addresses anyway? Pete On Aug 14, 7:19 pm, Mea wrote: address'Still trying to solve a problem with showing cell address'. I can determine if the "name" if there, and how many occurences there are (Thanks Pete_UK), but I need to show the cell address for EACH occurence and that's where it goes west. I came across a post from Ron that gave me this formula to return a cell address. I am using a range, and want something in the field only if there is an occurence, so I have used this. =IFERROR(ADDRESS(SUMPRODUCT(('working with files\southbed=$B4)*ROW('working with files\southbed)),SUMPRODUCT(('working with files\southbed=$B4)*COLUMN('working with files\southbed)))," ") However, this only works when there is one location. For multiple locations, I get one result and it's way off. How can I show cell address for all occurences? I'd also like the cell address to show as CZ613, rather than $CZ$613. Any way to do this? Thanks, Mea |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
showing cell locations per answer from Ron Coddere - stuck! | Excel Discussion (Misc queries) | |||
hyperlink with cell info in subject line | Excel Discussion (Misc queries) | |||
Excel Formulas are showing relative address, rather than absolute | Excel Worksheet Functions | |||
Hyperlink mailto - ref cell for mail subject | Excel Worksheet Functions | |||
How do I include cell data in subject line of email address/hyperl | Excel Worksheet Functions |