Need Help With Code - Cell Value Based On Values in Another Worksheet

[ZooTV92]

Sample Attached.

Sample.xlsx

I'm looking for advice on how to code a range of cells that will have a value based on a name in an adjacent cell. The code will look at the same name on another worksheet and concatenate two cell values next to the name.

I've [hopefully] explained it better in the sample.

Thanks for looking!

[rylo]

Hi

why don't you just use formulas?

Please Login or Register  to view this content.

rylo

[ZooTV92]

I am already using a variation of that formula, but there are thousands of these formulas that are bogging down the spreadsheet. I am trying to not only speed up and shrink the size of the spreadhseet, but learn how to code as well. The answer to my initial question should open up some new thoughts as to how I approach some of the other issues I have.

[StevenM]

Please Login or Register  to view this content.

[ZooTV92]

I will try this tomorrow night after work if I'm awake (I work a 17 hour day today, then I get to come back Wednesday morning and do another 9 hour day).

Thanks so much for the help!

[ZooTV92]

I can't seem to adapt this to my spreadsheet. I thought I could just look at and suit to my needs. I changed the sample workbook around to reflect the exact columns the information appears in my workbook. Maybe that will help.

Also I didn't think to add that the names as they appear on the spreadsheet will change quite often. A drop down menu is used to insert names, and as soon as the name is dropped, the concatenated string should change immediately to reflect the name change and its corresponding value.

Maybe if I see the code in the sample workbook it would also help me better understand how this should work.

Thanks again for taking time to help me understand all this.

New sample attached:

Sample 2.xlsx

[StevenM]

Place this code in a standard module.
On Sheet 1, add "=concatenate2numbers(L1)" to S1
Just like any other formula, you will see the name Concatenate2Numbers show up as you type.
Copy cell S1 to the rows below.

Please Login or Register  to view this content.

[StevenM]

Or you could just add in S1 the following:

Please Login or Register  to view this content.

[ZooTV92]

My bad for not mentioning this, but there will be several occasions where no name will appear in column L on the first sheet. I tried fiddling around with the code to make sure the cells in column S appear blank if that is the case, but I can't seem to get that to work.

Any thoughts?

The code in general works as intended, so thank you very much for that!

[ZooTV92]

Sleep deprivation is bad

=IF(L8 = "","",Concatenate2Numbers(L8))

Thanks again.