Thanks Rolf,
I sent the link home to my wife and I'll ask her to use her paypal account to send the well deserved money your way. She is getting off of work now and will be home to take care of this shortly.
Your short and simple VB programming removed a headache that I was not able to use typical formula's and cleverness to circumvent, and solved 99% of my task. After I finish with the paypal, I'll hit you with another little issue that you can probably easily solve. I'll e-mail you a short copy of the actual spreadsheet so you can see exactly what I'm doing, but I'll explain below for anyone that may be following this thread.
-------------------------------------------------------
Short question - (long and painful description follows). I have information that will more or less randomly fill in ONLY a single cell, either A1, B1 or C1, and the 2 remaining cells that are not filled will return the value of "#N/A" because they are VLOOKUP formula's that don't return a value. How can I concatenate A1, B1 and C1 into a single value/single cell when two of them are "#N/A" and one value will be a date value (or text name)?
-------------------------------------------------------
Long and painful description:
For the sake of simplicity, let's say there are 4 tabs. Tabs 2, 3 and 4 are data tabs (AT&T, Verizon Wireless and Sprint) with phone numbers, eligibility dates, customer names and vendor names. Tab 1 is the interface tab and will have 3 columns and 5 rows. Row 1 will be where you enter the wireless number that you need to look up from the data tabs 2, 3 and 4, so cell A1 says "enter wireless number here", with an arrow pointing to the cell on the right (Cell B1) for the directionally challenged idiots. Cell C1 is where I referenced your VB programming that returns only the 10 numbers entered in cell B1. Row 2 will be titles-Column A "Eligibility Date", Column B "Customer Name" and Column C "Vendor Name".
Row 3 references the AT&T data tab 2 and has the formula "=VLOOKUP(VALUE($C$1),'AT&T OCT 2010'!$A$2:$E$16613,2,FALSE)" and the "2" in the col_index_num portion of the formula returns the "Eligibility Date", "3" returns the "Customer Name" and "4" returns the "Vendor Name" reference.
Row 4 is the same as above, but references the VZW data tab 3.
Row 5 is the same as above, but references the Sprint data tab 4.
I also use conditional formatting to color/fill the "Eligiblity Date" cell green or red, depending on the date value returned. If the date is today or in the past, the fill color is green, and if the date is in the future (thus, not eligible for an upgrade) the fill color is red...and of course, I have the errors hidden to enhance people's viewing pleasure.
So, if I enter cellular number 918-555-1212 in cell B1, your formula returns 9185551212 in cell C1. Let's say the number is an AT&T number and is eligible for an upgrade, so cell A3 would show the eligibility date of 1/26/2010 and the cell would be green. Cell B3 would have the customer name "John Doe" and cell C3 would say "AT&T Mobile". Since the number wasn't on the VZW or Sprint tab, rows 4 and 5 would remain blank.
So basically, if the number is an AT&T number, it fills in Row 3, and row 4 & 5 are blank. if the number belongs to VZW, the information fills on Row 4, and rows 3 & 5 are blank, and if the number belongs to Sprint, the information is fills in on Row 5, and rows 3 and 4 are blank.
Is there way to put the information into a single row? Concatenate and the "&" functions don't want to work, or I'm not doing something correctly.
Actually, I have had enough interruptions that my wife made it home and we made the paypal transaction a moment ago. :)
And I am now sending the actual spreadsheet to your e-mail with about 20 randomized numbers for an example.
EDIT:
Wow, I'm glad this posted...when I hit submit, my computer locked up so hard the mouse wouldn't move and the clock wouldn't change, and I didn't have a copy of all of this...(wiping brow)