Dynamic linking or referencing of cells

[karthik]

Hi,

I am writing a hobby application for which has the following:

Worksheet --> a cells B5:E5
B6:E6
B7:E7

Worksheet -->b cells C5:F5 These cells contain the data
C6:F6
C7:F7

Now I want to do the following

a) link the data from Worksheet --> b to the cells in worksheet --> a
but I have the following problem,
When I click a "done" button for a certain row (say 5), C5 should be
deleted, and new data should be entered in the cells C5, C6, C7, to
F5, F6, F7 and now worksheet --> a should get automatically updated.

Is there any way to do it?


warm regards

karthik

 

[Don Guillett]

Examples of before/after

 

[karthik]

Hi Don,

This is an example

Worksheet "B"

B C D E
5 Apple Pie Fruits 20.08.2007
6 Orange Juice Citrus 21.08.2007

Worksheet "A"

G H I
J K
21 Apple Pie Fruits 20.08.2007
Checkbox_for_done
22 Orange Juice Citrus 21.08.2007
Checkbox_for_done

Cells in worksheet A should be linked to worksheet B, I have managed
to do that with a simple =B!B5 etc

Now when I click Checkbox_for_done, the corresponding item in
worksheet "B" should be deleted/moved to another worksheet, their rows
shifted by 1 up, and worksheet "A" re-populated.

In the macro, for checkbox_for_done, I was thinking of using
something like this

Worksheets(sheetName).Cells(newRow, 2).Value =
Worksheets("Dashboard").Range("K35").Value

Now, the problem is two fold:

a) How to choose a row corresponding to the one that has been
clicked?

b) How to pass the data into the sub/macro

b) how to alter worksheet "B" and then repopulate worksheet "A"?


Thanks in advance,

karthik