Row() function

[eao]

I want to use the Row() function so that I get the value of column B and use the Row function to dynamically lookup the row, but I can't figure out how to use it to build the cell address. Anyone help me out?

 

[wimvh]

Could you explain a little more? Maybe a small sample table.

 

[eao]

I'm trying to take a pivot table that has ones all over the place, create a formula that finds all the ones, and replaces the ones with the column title, all in a new field.

Sounds easy, right? Well, there are 50 rows and 618 columns.

But basically, the table looks like this, followed by the result:

Person Dog Cat Fish Result
Bob 1 1 Dog, Fish
Wanda 1 1 Cat, Fish
Burt 1 Dog

 

[eao]

My formula looks like this so far:

(IF(B5=1,REPLACE(B5,1,,B4) & ", ","")) &
(IF(C5=1,REPLACE(C5,1,,C4) & ", ","")) &
(IF(D5=1,REPLACE(D5,1,,D4) & ", ","")) & 
.
.
.
(IF(AY5=1,REPLACE(AY5,1,,AY4) & ", ",""))

But I can't paste this on row 6, because Excel changes the last reference to the cell above, not the title cell. So it becomes:

(IF(B6=1,REPLACE(B6,1,,B5) & ", ",""))...

 

[wimvh]

Change the reference from B4 to B$4, from C4 to C$4, ... to make it an absolute reference.

 

[eao]

I think this might work:

=(IF(OFFSET(B$1,(ROW()-1),)=1,REPLACE(OFFSET(B$1,(ROW()-1),),1,,OFFSET(B$4,,)) & ", ",""))

 

[AnotherHiggins]

Fist, a question:

Sounds easy, right? Well, there are 50 rows and 618 columns.

Did you mix up Rows and Columns? You do not have 618 columns on a single sheet. I know because Excel's max is 256. Or is you data spread across several worksheets?

But whatever the answer, the fact is that you are over-engineering this. Forget about REPLACE. just refer to the target cell.

Something like this:
[COLOR=blue white]=IF(B5="","",$B$4&", ")&IF(C5="","",$C$4&", ")&IF(D5="","",$D$4)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.