Please help me out with a Excel formula

[willgyn]

I'm developing a testing platform. I'm struggling to come up with a formula in Excel for the following tasks

• Add Pass/Fail/Advisory dropdown list.
• When Fail/Advisory selected from the dropdown list. The user will have to write a few lines about the outcome results. Then all the results then should be linked to a different Excel file (report page) where the outcome can be displayed and updated automatically.
• The report page needs to be capable of being saved independently of any other pages which it is pulling data in from so we can email the report.

I can't find a formula which update a Excel sheet with data from different spreadsheets. All the should be done seamlessly without the user having to copy and paste any text.


Could anyone please help me out.
I'm using Excel 2011 for mac

 

[oldbrewer]

I select "fail" and a box pops up telling me to go to "outcome" sheet

on the outcome sheet are instructions to fill the next vacant row with relevant details, eg audit date, auditor, auditee, name of audit

assuming there are 5 non conformances, on a new sheet say "report" typing in the audit date and name of audit will bring up all the details to be emailed to auditee

would this approach meet your needs ?

 

[willgyn]

I have around 100 test. The results will change all the time the test is taken.

I just need a formula that when Fail and Advisory is selected all the user comments will be automatically save in the report sheet.

Sub Button4_Click()
q1_answer = Sheets("Create New Presentation").Range("G4")

a3_answer = Sheets("Create New Presentation").Range("H4")

row_number = 1
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets(“Sheet1”).Range("B" & row_number)

Loop Until item_in_review = " "


Sheets(“Sheet1”).Range(“B” & (row_number)) = a3_answer


End Sub

This is what I got so far. But I didn't get anywhere

thanks

 

[oldbrewer]

SO I AM DOING YOUR TEST, Q1 is what is 2+2 I put 3 my answer is compared to "correct" answer and the message "fail" comes up.

I am then invited to add comments, these comments are saved on a separate sheet ?? is this broadly correct ?

 

[willgyn]

Yeah, something like that.

[/URL][/IMG]

I need the information from Description and Defect/ Comments when one of the options are selected.

so it looks like this one

[/URL][/IMG]

 

[oldbrewer]

a macro to copy, paste, special, values B2 and G2 to the next row on your "analysis" sheet ? and then clear the user inputs

 

[willgyn]

yes that is it! I was think of a submit button at the end of each stage as well.

 

[oldbrewer]

x=cells(2,7)
y=cells(2,2)
Sheets("analysis").Select
Sheets("analysis").Activate
comment - assume cell T1 on analysis sheet is a count of rows used + 1

cells(cells(20,1),2)=y
cells(cells(20,1),7)=x
sheets("sheet1").select
sheets("sheet1").activate

comment put youe delete code here

its a start.........

 

[willgyn]

I going to start working on it today. Thank you so much for your help.