Help!!

Kaza153

New Member
Joined
Jul 17, 2010
Messages
6
I am a newbie!! ;)

I have created a spreadsheet and need a little bit of help.

I have 2 worksheets on 1 workbook

Sheet 1 = Blank Form
Sheet 2 = Central Sheet

basically... (hahaha)
I need the sum of Blank Form H8 to equal onto Central Sheet B2 on the condition that Blank Form C6 = Mike & H6 = Jan-10 then lock data to that cell (as when the data from blank form is deleted/changed the information stays the same).

I have managed to get the sum to transfer with
=SUMIF('Blank Form'!C6,"Mike",'Blank Form'!H8)

however when i clear the data out from Blank Form Central Sheet B2 is then reset.

Please Help!
Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

At least 50 people have looked at this post but nobody has replied. That suggests to me that nobody understands the problem well enough to offer a possible solution. I suggest that you try to explain clearly, in more detail what you have and what you are trying to achieve. You might also think about illustrating with a small screen shot or two. For that you can use ...

Excel jeanie
or
RichardSchollar’s beta HTML Maker
or
Borders-Copy-Paste
 

Kaza153

New Member
Joined
Jul 17, 2010
Messages
6
Thanks...

how can i attach an image - it asks for a url?

Sheet 1 = Blank Form
Sheet 2 = Central Sheet

basically... (hahaha)
I need the sum of Blank Form H8 to equal onto Central Sheet B2 on the condition that Blank Form C6 = "Mike" & H6 = "Jan-10" then lock data to that cell (so when the data from Blank Form is deleted/changed the information in Central Sheet B2 remains).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
how can i attach an image
I provided links for three methods in my last post. ;)

I think the method you have tried requires you to have your image hosted by a public file-hosting site. You can do that, but you are likely to get less help here than if you use one of the methods I suggested.

The reason is that helpers don't usually like having to look at an image on another site and manually type in sample data themselves. Far easier to have the image directly in this forum in front of your and be able to copy the sample data and paste it straight into your own sample workbook.
 

Kaza153

New Member
Joined
Jul 17, 2010
Messages
6

ADVERTISEMENT

ok... lets see if this works

Attachment - C:\Users\LYNN\Documents\EJHTMLe\TempJean.htm

Cell H8 on blank form = a score.
I need this score to appear on the Central Sheet in corresponding cells.

This needs to be conditioned by cell C6 (name) and cell H6 (date) on the Blank Form.

The score that is then populated on the Central Sheet in for example cell B2 needs to remain in there even if the information on the blank sheet is deleted.

FYI - I am using excel 2003
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
ok... lets see if this works

Attachment - C:\Users\LYNN\Documents\EJHTMLe\TempJean.htm
When in the Excel jeanie interface and you have made the selections/choices you want, press the 'Forum Standard' button. The code you need will then be on your Clipboard so just move back to your post and 'Paste'. All you will see is a lot of HTML code but when you submit your post it should appear OK. I suggest you test first in the Test Here forum.
 

Kaza153

New Member
Joined
Jul 17, 2010
Messages
6

ADVERTISEMENT

Excel Workbook
ABCDEFGHI
1
2Feb 10 Sales Activity Management Form v1
3
4Evaluator:Account No/ MSISDN
5
6Agent:Interaction Date:Jan-10
7
8Score:0.00%
9
Blank Form
Excel Workbook
ABCDEFGHIJKLMNO
1MikeCatherineKaren DAshleighClaireCathyBridgetBenJonnerKayleighManishaLesleyGraemeKaren B
2Jan-100.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
3Feb-10
4Mar-10
5Apr-10
6May-10
7Jun-10
8Jul-10
9Aug-10
10Sep-10
11Oct-10
12Nov-10
13Dec-10
14
Central Sheet
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
Glad to see you eventually got Excel jeanie working. :)

You will need vba to accomplish your goal. You haven't said just would trigger the transfer of data from the form to the Central Sheet, but here is one way.

1. On the 'Blank Form' sheet, create a 'Submit' cell. I have used A8. Double-clicking this cell will trigger the transfer once you have entered all the data.

2. Right click the 'Blank Form' sheet name tab and choose 'View Code'.

3. Copy the code below and Paste into the main right hand pane that opens at step 2.

4. Close the VB window, enter data on the form and double click A8. The data should then be on the Central sheet.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> AgentCell <SPAN style="color:#00007F">As</SPAN> Range, DateCell <SPAN style="color:#00007F">As</SPAN> Range, ScoreCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> AgentHeader <SPAN style="color:#00007F">As</SPAN> Range, DateHeader <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> AgentCell = Range("B6"): <SPAN style="color:#00007F">Set</SPAN> DateCell = Range("H6"): <SPAN style="color:#00007F">Set</SPAN> ScoreCell = Range("H8")<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$8" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'<-- Change this to match your 'Submit' cell</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> AgentCell.Value = "" <SPAN style="color:#00007F">Or</SPAN> DateCell.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Cannot transfer data while Agent or Date is incomplete"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Sheets("Central Sheet")<br>                <SPAN style="color:#00007F">Set</SPAN> AgentHeader = .Rows(1).Find(What:=AgentCell.Value, LookAt:=xlWhole, _<br>                    MatchCase:=False, SearchFormat:=False)<br>                <SPAN style="color:#00007F">Set</SPAN> DateHeader = .Columns(1).Find(What:=DateCell.Value, LookAt:=xlWhole, _<br>                    MatchCase:=False, SearchFormat:=False)<br>                <SPAN style="color:#00007F">If</SPAN> AgentHeader <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> DateHeader <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    MsgBox "Unable to transfer data"<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    .Cells(DateHeader.Row, AgentHeader.Column).Value = ScoreCell.Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>


Excel Workbook
ABCDEFGH
4Evaluator:Account No/ MSISDN
5
6Agent:ClaireInteraction Date:May-10
7
8SubmitScore:13.60%
9
10
11
12
13
141.00%
152.60%
1610.00%
17
Blank Form



Excel Workbook
ABCDEFGH
1MikeCatherineKaren DAshleighClaireCathyBridget
2Jan-10
3Feb-10
4Mar-10
5Apr-10
6May-1013.60%
7Jun-10
8Jul-10
9Aug-10
Central Sheet
 

Kaza153

New Member
Joined
Jul 17, 2010
Messages
6
Thanks for your help on this one...

I have done exactly as you have advised and used cell A8 for the Submit button, i get the error message "Cannot transfer data while Agent or Date is incomplete".

Also... as soon as the data is removed from the Blank form the data on central sheet is removed... Is there a way of locking the data to the central sheet even if the Blank Form is Blank?

Thanks :) :confused: :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
Thanks for your help on this one...

I have done exactly as you have advised and used cell A8 for the Submit button, i get the error message "Cannot transfer data while Agent or Date is incomplete".

I think I may have used a different cell to you for the Agent name. I used B6 but I think you might have C6. Just change that in the code.


Also... as soon as the data is removed from the Blank form the data on central sheet is removed... Is there a way of locking the data to the central sheet even if the Blank Form is Blank?

Thanks :) :confused: :)
There is nothing in my code that removes anything from the Central Sheet - apart from perhaps over-writing one cell at a time when the 'Submit' cell is double-clicked. If data is being removed, then you must have something else going on. Perhaps other vba code, or formulas still on the Central Sheet that return blanks when the Form is blank. There should be no formulas on the Central Sheet for my code to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top