On a worksheet “ListsAmpl” there are two dynamic named ranges side by
side of equal length SysNamListDesig and SysNamList. …ListDesig
contains alphanumeric designators and …List contains corresponding
Titles. (I could just use one name as well if need be)

On a separate worksheet "Data Entry" there are several columns of data
and I need to scan the data and test each cell to see if any of the
SysNamListDesig items are present and if they are insert the
corresponding title after the designator in the cell.

Any help will be appreciated.
Robert

Sounds like the "VLookUp" function would do the job.
--
Jim Cone
Portland, Oregon USA



"Robert H"
wrote in message
On a worksheet “ListsAmpl” there are two dynamic named ranges side by
side of equal length SysNamListDesig and SysNamList. …ListDesig
contains alphanumeric designators and …List contains corresponding
Titles. (I could just use one name as well if need be)

On a separate worksheet "Data Entry" there are several columns of data
and I need to scan the data and test each cell to see if any of the
SysNamListDesig items are present and if they are insert the
corresponding title after the designator in the cell.

Any help will be appreciated.
Robert

The data entry sheet uses data validation to enter all data (several
columns). The titles that I need to append to the designators are
intenionaly left off during the data entry to minimize clutter for the
users. I then create a report worksheet that includes the titles and
some other modifications. Currently what I do is "copy all" the data
entry sheet "paste - special - values" so that just values exist on
the report sheet and I manual append the titles to the cells with
designators.

I cant see how to paste the data to the new sheet and at the same time
input a formula that uses the vlookup function.

Thanks for the reply



that I need to append to the to creat the On Jun 15, 6:14*pm, "Jim
Cone" wrote:
Sounds like the "VLookUp" function would do the job.
--
Jim Cone
Portland, Oregon *USA

"Robert H"
wrote in message
On a worksheet “ListsAmpl” *there are two dynamic named ranges side by
side of equal length SysNamListDesig and SysNamList. *…ListDesig
contains alphanumeric designators and …List contains corresponding
Titles. *(I could just use one name as well if need be)

On a separate worksheet "Data Entry" there are several columns of data
and I need to scan the data and test each cell to see if any of the
SysNamListDesig items are present and if they are insert the
corresponding title after the designator in the cell.

Any help will be appreciated.
Robert

Also I was hoping end the end to send the report worksheet formula
free. thats why I was lookng for a VBA solution that would insert the
titles. Which would eleminate another copy and paste values step.

On Jun 16, 3:38*pm, Robert H wrote:
The data entry sheet uses data validation to enter all data (several
columns). *The titles that I need to append to the designators are
intenionaly left off during the data entry to minimize clutter for the
users. *I then create a report worksheet that includes the titles and
some other modifications. Currently what I do is "copy all" the data
entry sheet "paste - special - values" so that just values exist on
the report sheet and I manual append the titles to the cells with
designators.

I cant see how to paste the data to the new sheet and at the same time
input a formula that uses the vlookup function.

Thanks for the reply

*that I need to append to the to creat the On Jun 15, 6:14*pm, "Jim



Cone" wrote:
Sounds like the "VLookUp" function would do the job.
--
Jim Cone
Portland, Oregon *USA

"Robert H"
wrote in message
On a worksheet “ListsAmpl” *there are two dynamic named ranges side by
side of equal length SysNamListDesig and SysNamList. *…ListDesig
contains alphanumeric designators and …List contains corresponding
Titles. *(I could just use one name as well if need be)

On a separate worksheet "Data Entry" there are several columns of data
and I need to scan the data and test each cell to see if any of the
SysNamListDesig items are present and if they are insert the
corresponding title after the designator in the cell.

Any help will be appreciated.
Robert- Hide quoted text -

- Show quoted text -

It is still a VLookUp solution.
VLookup will place the titles on the input sheet.
You copy the two input columns and paste values on the report sheet.
You close the original book with out saving (no vlookup formulas will remain)
--
Jim Cone
Portland, Oregon USA
(ex 3Com - Santa Clara, California)



"Robert H"
wrote in message
Also I was hoping end the end to send the report worksheet formula
free. thats why I was lookng for a VBA solution that would insert the
titles. Which would eleminate another copy and paste values step.

On Jun 16, 3:38 pm, Robert H wrote:
The data entry sheet uses data validation to enter all data (several
columns). The titles that I need to append to the designators are
intenionaly left off during the data entry to minimize clutter for the
users. I then create a report worksheet that includes the titles and
some other modifications. Currently what I do is "copy all" the data
entry sheet "paste - special - values" so that just values exist on
the report sheet and I manual append the titles to the cells with
designators.

I cant see how to paste the data to the new sheet and at the same time
input a formula that uses the vlookup function.

Thanks for the reply

that I need to append to the to creat the On Jun 15, 6:14 pm, "Jim



Cone" wrote:
Sounds like the "VLookUp" function would do the job.
--
Jim Cone
Portland, Oregon USA

"Robert H"
wrote in message
On a worksheet “ListsAmpl” there are two dynamic named ranges side by
side of equal length SysNamListDesig and SysNamList. …ListDesig
contains alphanumeric designators and …List contains corresponding
Titles. (I could just use one name as well if need be)

On a separate worksheet "Data Entry" there are several columns of data
and I need to scan the data and test each cell to see if any of the
SysNamListDesig items are present and if they are insert the
corresponding title after the designator in the cell.

Any help will be appreciated.
Robert- Hide quoted text -

- Show quoted text -

Sorry Jim, but I am confised
Were is the vlookup formula?
are you sugesting that I have a column for the designators and one for
the titles?
I dont see how this gets both into one cell (column)

On Jun 16, 6:35*pm, "Jim Cone" wrote:
It is still a VLookUp solution.
VLookup will place the titles on the input sheet.
You copy the two input columns and paste values on the report sheet.
You close the original book with out saving (no vlookup formulas will remain)
--
Jim Cone
Portland, Oregon *USA
(ex 3Com - Santa Clara, California)

"Robert H"
wrote in message
Also I was hoping end the end to send the report worksheet formula
free. thats why I was lookng for a VBA solution that would insert the
titles. *Which would eleminate another copy and paste values step.

On Jun 16, 3:38 pm, Robert H wrote:



The data entry sheet uses data validation to enter all data (several
columns). The titles that I need to append to the designators are
intenionaly left off during the data entry to minimize clutter for the
users. I then create a report worksheet that includes the titles and
some other modifications. Currently what I do is "copy all" the data
entry sheet "paste - special - values" so that just values exist on
the report sheet and I manual append the titles to the cells with
designators.

I cant see how to paste the data to the new sheet and at the same time
input a formula that uses the vlookup function.

Thanks for the reply

that I need to append to the to creat the On Jun 15, 6:14 pm, "Jim

Cone" wrote:
Sounds like the "VLookUp" function would do the job.
--
Jim Cone
Portland, Oregon USA

"Robert H"
wrote in message
On a worksheet “ListsAmpl” there are two dynamic named ranges side by
side of equal length SysNamListDesig and SysNamList. …ListDesig
contains alphanumeric designators and …List contains corresponding
Titles. (I could just use one name as well if need be)

On a separate worksheet "Data Entry" there are several columns of data
and I need to scan the data and test each cell to see if any of the
SysNamListDesig items are present and if they are insert the
corresponding title after the designator in the cell.

Any help will be appreciated.
Robert- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

"I don't see how this gets both into one cell (column)"
My assumption was you had two columns on both sheets.

In any case,
assuming two columns on ListsAmpl sheet (A1:B20) containing: ListDesig | Title
assuming one column on Data Entry sheet (A1:A20) containing: ListDesig or ListDesigTitle

Then use this formula in column B on Data Entry...
=IF(ISNUMBER(MATCH(A1,ListsAmpl!$A$1:$A$20,0)),A1 &INDEX(ListsAmpl!$A$1:$B$20,MATCH(A1,ListsAmpl!$A$ 1:$A$20,0),2),A1)
--
Jim Cone
Portland, Oregon USA



"Robert H"
wrote in message
Sorry Jim, but I am confised
Were is the vlookup formula?
are you sugesting that I have a column for the designators and one for
the titles?
I dont see how this gets both into one cell (column)



On Jun 16, 6:35 pm, "Jim Cone" wrote:
It is still a VLookUp solution.
VLookup will place the titles on the input sheet.
You copy the two input columns and paste values on the report sheet.
You close the original book with out saving (no vlookup formulas will remain)
--
Jim Cone
Portland, Oregon USA
(ex 3Com - Santa Clara, California)

thanks Jim, I cant get to the preadsheet today but will work with that
tomorrow or Friday.
Robert
On Jun 17, 10:13*am, "Jim Cone" wrote:
*"I don't see how this gets both into one cell (column)"
My assumption was you had two columns on both sheets.

In any case,
* assuming two columns on ListsAmpl sheet (A1:B20) containing: ListDesig | Title
* assuming one column on Data Entry sheet (A1:A20) containing: ListDesig or ListDesigTitle

Then use this formula in column B on Data Entry...
=IF(ISNUMBER(MATCH(A1,ListsAmpl!$A$1:$A$20,0)),A1 &INDEX(ListsAmpl!$A$1:$B$20,MATCH(A1,ListsAmpl!$A$ 1:$A$20,0),2),A1)
--
Jim Cone
Portland, Oregon *USA

"Robert H"
wrote in message
Sorry Jim, but I am confised
Were is the vlookup formula?
are you sugesting that I have a column for the designators and one for
the titles?
I dont see how this gets both into one cell (column)

On Jun 16, 6:35 pm, "Jim Cone" wrote:



It is still a VLookUp solution.
VLookup will place the titles on the input sheet.
You copy the two input columns and paste values on the report sheet.
You close the original book with out saving (no vlookup formulas will remain)
--
Jim Cone
Portland, Oregon USA
(ex 3Com - Santa Clara, California)- Hide quoted text -

- Show quoted text -