Help with "If"

  • Thread starter Thread starter Old_skills_lost
  • Start date Start date
O

Old_skills_lost

Here is what I need to do I have been working on a sales sheet that will
track the sales via a won loss ratio. Now what I have been trying to do is to
take all of the information from the "activity" sheet and copy or perfer move
it to a sheet based as "lost" or "Won". What I have worked out is the
following (r/c is short for row/colum)

=if(r/c="won", 'Won'! ,if(r/c="lost", 'Lost'! ,))

I think I have the start of this formula correct but as you can see i am
missing several key parts.
 
You mention you want to copy or move the activity sheet to another sheet
based on won/lost, but you are not specific so it will be difficult to tell
you exactly how to do what you want. Are you going to have a "Lost" sheet
and a "won" sheet? What do you want to move?

Les
 
There are a Won and Lost sheets already made and i need to move or copy the
info from the activity sheet and the row when the colum on that rows outcome
has been changed to Won or Lost to the correct page.
 
The more I think about this, the more I believe you need a macro in order to
avoid blank lines within the won sheet or lost sheet. Try posting your
question within the programming section of the Excel forum and see what
happens. If you are new at this, let them know that. I have an idea of how
to do it but there are people in that forum who could write you the code.

Good luck,
Les
 
Correct me if I am wrong but I need top have a formula for this instead of a
Macro as Macro's have to be ran and are not automatic as soon as the colum is
changed. As where a formula can be made to do it after it is done.
 
Yes a macro can be called by a cell change. The code below executes whenever
a cell in the range A1 - A10 is changed. Right click a sheet tab, view code
paste it in and try it.

Achieveing what you seem to want though would require more detail

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
MsgBox Target.Address & " Changed"
End If
End Sub

I am not proficient in programming VBA, though I have done some. My using a
macro will avoid blank lines on Sheet(Won) & Sheet(Lost) because a formula in
a spreadsheet can only reference a single cell value and cannot look at a
range on a "master" sheet, ie row 1 of Sheet(won) references row1 of master
sheet. If Sheet(Master)Row2 is lost and Sheet(Master)Row 3 is won, then
Sheet(Won)Row2 will be blank.

A macro however, can pull data in and place on a sheet without skipping lines

Hope this helps you out.
Les
 
Back
Top