Listing text mentions in Excel

[rmilos]

I have a project where I need to match my company's disciplines with the previous client projects.

I have sheet one where I will list a client name in column A1 with disciplines used for that client in B1, C1, D1, etc.
Client two in A2 with discipline in B2, C2, etc.

Sheet two contains the disciplines in A1, B1, C1, D1, etc.

Is there a formula the hat would return a list of client names with that discipline on sheet 1 under the discipline heading?

 

[rmilos]

I copied the formula off the web into Notepad. With CTRL C, copied it again.

I selected the cell (A2) put the cursor in the fx field, CTRL V. Did a CTRL Shift Enter.
Then selected B2, highlighted the formulain the fx field, CTRL V. Did a CTRL Shift Enter.

on to the next cell

All read Adidas now.

 

[Eric W]

That's the issue. When you copy a formula that way, you're getting the exact same formula in both cells, so of course you'll get the same answer. But when you select a cell, type Ctrl-C, select another cell, and type Ctrl-V, then Excel automatically modifies the formula before pasting it. Excel will change any relative cell references in the formula to be relative to the cell you are pasting the formula to.

For example:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]=A1+5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If you copy the formula in B1 by putting it in the formula bar of B2 and B3, you get:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]=A1+5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]=A1+5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]=A1+5[/TD]
[/TR]
</tbody>[/TABLE]

whereas if you select B1, press Ctrl-C, select B2:B3, and press Ctrl-V, you get:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]=A1+5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]=A2+5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]=A3+5[/TD]
[/TR]
</tbody>[/TABLE]

If you need to use an absolute reference, like a percentage rate that applies to every calculation, you need to add a $ to the cell reference, which tells Excel "don't change this reference".

If you look at the formula I gave you, it has some references with a $, and some without. Which means that it's very important to let Excel modify the references when you copy it.

As a bonus, if you copy the formula this way, you can select an entire range to paste it in. You don't need to enter the formula 1 cell at a time.

So to recap the steps:
1) Take the formula and paste it into the formula bar of A2 on sheet and confirm it with Control-Shift-Enter.
2) The selection box should still be on A2 - press Ctrl-C.
3) Select range B2:F2
4) Press Ctrl-V
5) Select range A3:F100 (this should be the maximum number of clients)
6) Press Ctrl-V

Give that a try and let me know.

 

[rmilos]

You Sir are a genius. It works. Thank you so much!

Ron

 

[Eric W]

Happy to help!