Keeping linked data after changing source.

  • Thread starter Thread starter Djanvk
  • Start date Start date
D

Djanvk

Here is what I'm wanting to try and do and just am looking for a push in
the right direction.

I have one sheet with names where people enter how many of a service to
charge for per shift of different items.

What I want is another sheet with all the items tallied up to be able
to enter the charges. I've decided to link the names to sheet2 from
sheet1 so as to not have to reenter. Here is the problem I'm haveing
to figure out:

When A person is deleted during the day (goes home), the text from the
link on sheet2 is removed to but I need to find a way to keep this
information on the other sheet as to do the charges later at night.

I guess I want to know how would I go about transfering the information
automatically to a sheet when charges are added and sum them, then not
lose this information when there person is deleted from sheet1 and even
another might be added into the same room.


Hope I explained this right, if not lmk and I'll try better.

Thanks a lot.
 
First, I don't like spreading my data into multiple worksheets. I find it much
more difficult to do stuff to it (pivottables, subtotals, charts, etc).

I think I'd try to keep it all on one sheet, but then add a column that shows if
the person is in or out.

Then apply Data|filter|autofilter to that range. From there, I could filter on
a person's name, or show all the people in (or out) with not too much trouble.

If I had to keep historical data, I could filter, then copy|paste to a new
worksheet. (but maybe just adding another column for date would give another
filter option.)
 
That wont work very well.

what this is, is a bed roster for a hospital for my department. And if
a patient leaves and the bed is again filled, I still want a record of
the patient previously in the bed, See what i'm saying I cant just put
in/out, if the patient is out, he is out.

If there is a patient is there a way to automatically move the
information into another sheet?


Thanks again for help.
 
That automatic stuff scares me. If automatic means that you click a button or
run a macro, then I get it. But I think typing an X in a cell and having excel
move it to a new worksheet is scary--not because xl can't do it--but I'm a lousy
typist.

If I put it in the wrong row, then I have more trouble putting everything back
where it belongs.

I'd do this: apply window freeze panes so I always have a row or two at the top
always visible. Then put a button from the forms toolbar in that frozen area
(so it's always visible).

Then assign it to a macro like this:

Option Explicit
Sub testme()

Dim iRow As Long
Dim destCell As Range

With ActiveSheet
iRow = ActiveCell.Row
If Application.CountA(.Rows(iRow)) = 0 _
Or iRow < 3 Then
MsgBox "Please select a row not in the header " _
& "and one that contains data"
Exit Sub
End If

With Worksheets("sheet2")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With .Rows(iRow)
.Copy _
Destination:=destCell
.Delete
Beep
MsgBox "Row: " & iRow & " moved!"
End With

End With

End Sub

It'll take the row with the activecell in it. If you're industrious, you could
add an "are you sure" message" and if you hate the confirmation, you could get
rid of that last msgbox.
 
Back
Top