Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
test mark
I currently have two work sheets in an excel document and I need to create a
chart to read the one sheet and fill in the other sheet with either an "x" or the mark. as an example the information is on sheet1 and I need it to read the persons name, and the course and then post it onto a second sheet called report. here is where it gets a little tricky. sheet 1 has 40000 lines so I need the macro to read through the 40000 lines and locate the name and then compair it to the secondary cell that has the course. for example lets say john smith in column A and the course Final Test in Column B. I then need the information to post either the "X" or the grade in the cell "D4" on sheet 2. If this is easier done using a input box, by all mean suggest that, but the hard code is appreciated as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200711/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
test mark
this is probably best done by using a vlookup on sheet2
=IF(ISNA(VLOOKUP(A1,Sheet1!A1:B40000,2)),"","X") "Darrell_Sarrasin via OfficeKB.com" wrote: I currently have two work sheets in an excel document and I need to create a chart to read the one sheet and fill in the other sheet with either an "x" or the mark. as an example the information is on sheet1 and I need it to read the persons name, and the course and then post it onto a second sheet called report. here is where it gets a little tricky. sheet 1 has 40000 lines so I need the macro to read through the 40000 lines and locate the name and then compair it to the secondary cell that has the course. for example lets say john smith in column A and the course Final Test in Column B. I then need the information to post either the "X" or the grade in the cell "D4" on sheet 2. If this is easier done using a input box, by all mean suggest that, but the hard code is appreciated as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200711/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
test mark
ISNA?
Joel wrote: this is probably best done by using a vlookup on sheet2 =IF(ISNA(VLOOKUP(A1,Sheet1!A1:B40000,2)),"","X" ) I currently have two work sheets in an excel document and I need to create a chart to read the one sheet and fill in the other sheet with either an "x" or [quoted text clipped - 11 lines] If this is easier done using a input box, by all mean suggest that, but the hard code is appreciated as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200711/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
test mark
VLOOKUP returns an NA error if it can't find the data. ISNA detects the error.
The ISNA will allow "" (nothing) to be displayed on the worksheet if data is not found. If data is found an X will appear. "Darrell_Sarrasin via OfficeKB.com" wrote: ISNA? Joel wrote: this is probably best done by using a vlookup on sheet2 =IF(ISNA(VLOOKUP(A1,Sheet1!A1:B40000,2)),"","X" ) I currently have two work sheets in an excel document and I need to create a chart to read the one sheet and fill in the other sheet with either an "x" or [quoted text clipped - 11 lines] If this is easier done using a input box, by all mean suggest that, but the hard code is appreciated as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200711/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
test mark
and if I want to use this in a macro how would it work. I have 40 agents
with 30 plus classes to do. example "john" is the name "certification" is the course Joel wrote: VLOOKUP returns an NA error if it can't find the data. ISNA detects the error. The ISNA will allow "" (nothing) to be displayed on the worksheet if data is not found. If data is found an X will appear. ISNA? [quoted text clipped - 7 lines] If this is easier done using a input box, by all mean suggest that, but the hard code is appreciated as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200711/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
test mark
You weren't specific about the sheet names and the column. the code below
will need some minor adjustments. The code checks every row in column A on the Report worksheets and then does a search on Sheet 1 Column A for the name. If it finds the name it copies the data in Column B to Column D. Sub FindGrades() With Sheets("Report") ReportRow = 1 Do While .Range("A" & ReportRow) < "" Name = .Range("A" & ReportRow) With Sheets("Sheet1") Set c = .Columns("A:A").Find(what:=Name, LookIn:=xlValues) End With If Not c Is Nothing Then .Range("D" & ReportRow) = c.Offset(rowoffset:=0, columnoffset:=1) End If ReportRow = ReportRow + 1 Loop End With End Sub "Darrell_Sarrasin via OfficeKB.com" wrote: and if I want to use this in a macro how would it work. I have 40 agents with 30 plus classes to do. example "john" is the name "certification" is the course Joel wrote: VLOOKUP returns an NA error if it can't find the data. ISNA detects the error. The ISNA will allow "" (nothing) to be displayed on the worksheet if data is not found. If data is found an X will appear. ISNA? [quoted text clipped - 7 lines] If this is easier done using a input box, by all mean suggest that, but the hard code is appreciated as well. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200711/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you help test our Excel test? | Excel Discussion (Misc queries) | |||
Show a tick mark for a positive test result | Excel Worksheet Functions | |||
Foot mark aka single quote mark? | Excel Worksheet Functions | |||
How can I add active tick mark/check mark boxes in excel? | Excel Discussion (Misc queries) | |||
Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) |