Auto filling of form letters

[SlimPickins]

I have a workbook with 4 worksheets in it. Sheet 1 is a list of work
locations. Sheet 2 is a form letter about work performed. Sheet 3 and
4 are "under construction".


How can I fill the form letter on Sheet 2 automaticly when a row from
Sheet 1 is selected or high lighted?

Or could I put a dropdown list which was made from a list on sheet 1.
See below.
Also how could I keep the dropdown list updated when new rows are
added to sheet 1?


Would if then work.

if
g8 = [dates from a dropdown list made from sheet1 a1 through a10]
then
c10 is x[x being information from different col but same row as
date from dropdown list above.]

????????

Slim

 

[Frank Kabel]

Hi
1. Automatic fillin g then row is selected:
- though this is possible this would require an event procedure (using
the Selection_Change event)
- also you have to define what should happen if the user select again
after filling sheet2
- I would not recomment this procedure

2. Drop-Down listboxes: I would go this way, using 'Data - Validation'
listboxes on sheet two. Try the following:
- create a name for your location list on sheet 1:
-> Assumption: your list is in column A (A1:Ax)
-> goto 'Insert - Name - Define'. Lets say you call this list
'locations'
-> enter the following formula
=OFFSET('sheet1'!$A$1,0,0,COUNT('sheet1'!A:A))
-> this formula ensures a dynamic range (as long as you don't have
blanks rows in between)

- On your second sheet select the cell for the drop-down (cell G8)
-> goto 'Data - Validation' and choose 'List'
-> Enter the following formula as data source:
=locations

- select cell C10. Assumption: you want to get the data from col. B on
sheet 1 for the corresponding value from col. A. Enter the formula
=IF(G8<>"",VLOOKUP(G8,'sheet1'!$A$1:$B$100,2,0),"")

 

[SlimPickins]

Frank

I have a small problem. Some dates have more than one entry, two rows
for two jobs done on the same date.

Could I insert a new column before A (make a - b and such) then format
this column to autofill with numbers (example 1 - 99999999) when a new
row is added.

The user could fill it but there is a chance of them screwing it up.

Slim