Test cell for list data and modify cell contents

[Robert H]

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

 

[Jim Cone]

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



"Robert H" <[email protected]>
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

 

[Robert H]

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

 

[Robert H]

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.

 

[Jim Cone]

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" <[email protected]>
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.

 

[Robert H]

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)

 

[Jim Cone]

re: "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" <[email protected]>
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)

 

[Robert H]

thanks Jim, I cant get to the preadsheet today but will work with that
tomorrow or Friday.
Robert