Cell Referencing question

**Ishdent** · 11-03-2011, 12:51 PM

I have about 170-180 sections of data in an excel document where I need to copy a value in a cell down an entire column conditional on another cell.

ex. I have the value "Word A" in cell A1-T1 and another value "Term A" in cell A7. How would I create a formula that copies "Term A" into cells B7-T7. I know that I could simply filter and copy down but is there a way which I can perhaps do that for all 180 sections at once?

**NBVC** · 11-03-2011, 01:17 PM

It's a bit unclear what you are looking for..

Perhaps a simple IF()

e.g.

=IF(A1="Word A",A$7,"") copied down.. but I am not sure what you mean by other groups... Maybe a sample workbook might help us to understand better what you need ultimately.

**Ishdent** · 11-03-2011, 01:43 PM

For all of the exact values in column A I want to return the value in column D that corresponds to the first unique value in column A. Does that make more sense?

**NBVC** · 11-03-2011, 02:00 PM

Does a pivot table like in the attached work for you?

**Ishdent** · 11-03-2011, 02:23 PM

Oooooooooooo so close! Would there be a way make a pivot table which returns text strings instead of numbers? Imagine replacing all of the numbers in the workbook with a letter. 1=a 2=b 3=c 4=d 45=de. I need to copy down specific urls for adwords.

**NBVC** · 11-03-2011, 02:42 PM

Not really... Pivot Tables are for number crunching....

You can create a unique list first. Select column A and go to Data and click Advanced from the Sort & Filter section... check Unique Records Only and select Copy to another location and point to where you want the result.. I chose H1.

Then you can have a formula in column I, starting at I2:

=INDEX(D:D,MATCH(H2,A:A,0))

copied down.

**Ishdent** · 11-03-2011, 05:50 PM

That didn't work completely for me but it did put me on the right track. I made the table and used Vlookup Instead. Thanks sooo much for your help dude!