Transferring Data

[Arvennis]

Hi All
I have a database from which I select a row of personal data to be updated in another page (for privacy reasons). My problem is: How can I find and transfer the updated data back to the correct position? I will be creating a macro to automate the process, but I cannot find a way to go to the correct row to inset the modified data.

I have uploaded a sample file which (hopefully) will show my dilemma. Thanks in advance for any ideas and tips
Arvennis

 

[kchiba]

Hi Arvennis,

I would use vlookup and or match/index and then copy/paste values once all the data is in place.

However, if I may suggest that you structure the file so that you have the transactions one below the other in a database format, this will enable you to do more analysis on the data.

I am not sure if you will update the whole record each time on add transactions as they happen

 

[SirJB7]

Hi, Arvennis!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, please clarify these points:
a) Cell B2 is the "primary key" for the "single table database" in worksheet Sheet2?
b) What if it doesn't exist? Should it be appended at the end or inserted as per its value?
c) If it exists, what "fields" (columns) should be updated, and under which conditions, if any applicable?
Thank you.

Regards!

 

[Marc L]

Hi Arvennis ,

unclear, even with the attached workbook …​

 

[Arvennis]

Hi All,
Thanks for replying so quickly.
As I am only new at this, I wasn't sure how to explain my problem correctly.

Kchiba, I appreciate your suggestion, but I don't need to do any analysis, all I need to do is keep a record of transactions and a running balance for each member, hence the need to pick up the correct "Member No" in sheet 2 and insert any updated data. the Database has 200 "Members" and their transactions, so it is quite a large database.

SirJB7 , Yes cell B2 is a "primary key" I insert a number here, and if there is data corresponding to that number in the database (sheet2), then all the other fields in sheet1 are populated by a Vlookup formula, I can then go to the next vacant field (in this case cell "N" and insert a monetary amount in cell "N", the date of the transaction in Cell "O" and the type of transaction ie "won" or "spent" in cell "P".

So Kchiba, SirJB7 and Marc L, My main problem is to get the updated data into the corresponding row of sheet2.
ie in my example, I need the cursor to go to cell "A7". I can Select "Sheet1 cells B2:AQ2, Copy, but how can I get the cursor to go to the corresponding "Member No." so I can Paste (values) the data.

I have created a data range called "MemberNO" and have tried using MATCH to give me the correct row number, but I cannot figure out how to get the cursor to go there. Hope this makes my problem a little clearer.

Thanks again for all your advice and imput.
Arvennis

 

[NARAYANK991]

Hi Arvennis ,

Obviously a macro will make things very easy for you , but in the meantime can you try the uploaded file ?

Narayan

 

[Ram Mher]

Instead of macro, you can use vlookup function to resolve this dilemma.

 

[kchiba]

Hi Arvennis,

In the attached spreadsheet is an example of finding the address for a formula in VBA, you can adapt it to suit your purpose. If you need more help with this let me know.

kanti

 

[Arvennis]

Hi all,
Thank you for your replies.

Narayan, This exactly what I need, I can use the formula in a macro to automate the process. Thank You.

Ram Mher, I considered using vlookup to transfer my data back to the correct place, but wouldn't that mean I need a lookup formula in every cell in the database, and that may cause more problems.

Kanti, Your reply seems to be on the right track, but I couldn't find your attachment.

Thanks to all who replied.
Arvennis

 

[kchiba]

@Arvennis,

Sorry that I did not upload the file and that I missed your post.

Here is the file, let me know if you need more help