Subject: showing cell address' per ans from Ron Coddere - still st

[Mea]

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

 

[Pete_UK]

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/micr...m/thread/7972d67f38c89618/8a76dd3011ba5fb6?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

 

[T. Valko]

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


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/micr...m/thread/7972d67f38c89618/8a76dd3011ba5fb6?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

 

[Mea]

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)*ROW(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!

 

[Mea]

I've just attached a better example as a reply to Pete above.

Thanks for the "4"! Works great!

Thanks for the