2 worksheet and dropdown list

[David]

I have 2 worksheets in 1 workbook, sheet 1 is a form that caculates results
based on a number put in each cell, i.e.,

text1 #(input cell)
result result result
**************************
text2 #(input cell)
result result result
**************************
text3 #(input cell)
result result result
**************************
text4 #(input cell)
result result result


the 2nd worksheet is a list of names and 4 values; like this

name1 value1 value2 value3 value4
name2 value1 value2 value3 value4
name3 value1 value2 value3 value4

I have named the 1st col to studentname and was able to create a dropdown
list on sheet 1, What I need to do is select a name from the dropdown list a
have it populate the proper cells on sheet 1 from the values on sheet 2

I hope this is enough detail

Thanks - David

 

[Frank Kabel]

Hi
sounds like VLOOKUP. Lets say your input cell on sheet 1 is A1 and you
want to get the first value from your list on the second sheet use
=VLOOKUP(A1,'sheet2'!$A$1:$E$100,2,0)
to get the second value use
=VLOOKUP(A1,'sheet2'!$A$1:$E$100,3,0)
and so on

 

[David]

Frank thanks, the validation dropdown list is on sheet 1, so when I select a
name from that list I want to get the value from sheet 2

thanks

 

[Frank Kabel]

Hi David
then the formula from below should do. If you have any probles getting
it to work just come back to this NG

 

[David]

Frank, you have been a great help, I had the source coming from the wrong
cell, once corrected it works exactly like I need - thank you very much.

 

[Frank Kabel]

Hi David
that's good to hear

 

[David]

Frank,

Would it be possible to have a command button on sheet 1, when pressed, it
would print out a sheet 1 for everyone in the list ?

 

[Frank Kabel]

David
yes possible. But what do you mean with 'for each person'. Should the
value change automatically for all persons? You may explain this a
little bit more

 

[David]

the validation list on sheet 1 contains student names from sheet 2, sheet 2
also has 4 values for each students, when I select a name from the
list(sheet 1) 4 values populate and caculations are made on sheet 1, then I
print out sheet 1 for each student. so I need a command button that would
start with the 1st student from the list(sheet 1) put the 4 values into the
cells as previously done, print sheet 1, then on to the next student, until
the end of the list(sheet 1)

hope that helps

 

[Frank Kabel]

Hi David

put the following macro in one of your workbook modules and assign a
button to it

----
Sub multi_print()
Dim wks_active As Worksheet
Dim wks_list As Worksheet
Dim rng_list As Range
Dim cell As Range

Set wks_active = ActiveSheet
Set wks_list = Worksheets("Sheet2")
Set rng_list = wks_list.Range("A1:A100")

For Each cell In rng_list
wks_active.Range("A1").Value = cell.Value
wks_active.PrintOut
Next
End Sub

 

[David]

Frank, I understand most of the code you sent except;

wks_list.Range("A1:A100")

is this the column of students on my sheet 2, if so can this be dynamic - it
changes every semester. this is a small spreadsheet, would it help if I
zipped it and sent it to ya to look at ? it contains the formula from our
previous conversations so it asks about enabling micros - which you can
disable for security.

thanks

 

[Frank Kabel]

Hi David
yes this is the list of your students. what do you mean with dynamic.
If this list can have different lengths one way would be the following

Sub multi_print()
Dim wks_active As Worksheet
Dim wks_list As Worksheet
Dim rng_list As Range
Dim cell As Range

Set wks_active = ActiveSheet
Set wks_list = Worksheets("Sheet2")
Set rng_list = wks_list.Range("A1:A500")

For Each cell In rng_list
if wks_active.Range("A1").Value <>"" then
wks_active.Range("A1").Value = cell.Value
wks_active.PrintOut
end if
Next
End Sub
----

I have defined a large list and the For-Next loop checks if an entry in
the specific cell exist. Only if this is true the sheet 1 is printed.
If you still have problems applying this you can mail me your
spreadsheet (Frank[dot]kabel[at]freenet[dot]de)

 

[David]

Frank, thanks, I will be away most of the day but will let you know how it
works out.

David

Hi David
yes this is the list of your students. what do you mean with dynamic.
If this list can have different lengths one way would be the following

Sub multi_print()
Dim wks_active As Worksheet
Dim wks_list As Worksheet
Dim rng_list As Range
Dim cell As Range

Set wks_active = ActiveSheet
Set wks_list = Worksheets("Sheet2")
Set rng_list = wks_list.Range("A1:A500")

For Each cell In rng_list
if wks_active.Range("A1").Value <>"" then
wks_active.Range("A1").Value = cell.Value
wks_active.PrintOut
end if
Next
End Sub
----

I have defined a large list and the For-Next loop checks if an entry in
the specific cell exist. Only if this is true the sheet 1 is printed.
If you still have problems applying this you can mail me your
spreadsheet (Frank[dot]kabel[at]freenet[dot]de)



--
Regards
Frank Kabel
Frankfurt, Germany

Frank, I understand most of the code you sent except;

wks_list.Range("A1:A100")

is this the column of students on my sheet 2, if so can this be
dynamic - it changes every semester. this is a small spreadsheet,
would it help if I zipped it and sent it to ya to look at ? it
contains the formula from our previous conversations so it asks about
enabling micros - which you can disable for security.

thanks