syntax to select cell in another sheet based on offset of active cell in current sheet

[duvius]

I have been trying to develop a client data input form that is referenced by a printable output form in another sheet. What I am struggling with is how to have my code make changes in the output form based on the cell input in the data entry form. For example, if I want to add a row in the data input form (Cell A17) and that corresponds to adding a row in the output form (Cell A7).

So my question is how can I write a code to create an offset based on active cell (active sheet) to another sheet?

[stnkynts]

Submit a workbook example

[duvius]

I would preferably like avoid using the message box, but that is the work around at the moment. Ideally, the corresponding addition in Networth just happens....

1. Go to Client Data. Click in Cell A24
2. Go to Networth. Click in A9.
3. Go back to Client Data. Click Add Row.
4. Now when you enter in new data in Row 24 it automatically gets added to Networth under the relevant section.

Trying to avoid step 2...

[stnkynts]

Please Login or Register  to view this content.

[duvius]

Okay. First off this is great! It is getting closer. However, there seems to be an issue if you add a row and the new row has the same type of account selected. For example, if you add a row under Cash Accounts and choose checking, when you add another row it finds the first "Checking" in Networth and fills down.

Can we set rFind = ActiveCell.Offset(-1,0).End(xlToRight) ??? That didn't quite work for me but I am trying to have it set all columns with values above lRow to be the range we will then search for in ws2.

[stnkynts]

I have no idea what you mean.

[duvius]

Ummm I am not sure what part of my reply you don't understand. First, when I actually put the code you wrote in action it does not work as desired.

For example, do the following:

1. Select Row 24. Hit Add Row
2. Choose "Checking", Steve and put in balance of 25,000
3. Now Select Row 25. Hit Add Row.
4. Choose "Savings", Samantha and balance of 50,000
5. Now go to Networth and look under "Cash Accounts"

Do you see what I mean? If a person has more than one Checking account it will find the first one it sees and fill that one down which creates a duplicate cell reference.

My additional comment was regarding a possible solution. It's not perfect, but it's unlikely that someone would have the same account, ownership, balance, account number, etc. So if you referenced all the values instead of just column A, it might avoid this.

I tried

but that didn't exactly work. Then I got stuck and replied back to you.

[duvius]

This thread was reposted in Commercial and solved. Thanks everyone!