+ Reply to Thread
Results 1 to 17 of 17

Copy data from column to other sheets, based upon vlookup/criteria on column a

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Copy data from column to other sheets, based upon vlookup/criteria on column a

    Good Day,

    I have an excel sheet that updates the main sheet "BUYER +" from the other 4 sheets in the workbook.

    This is all good and well, and works great. But I need values entered in columns K - N to reflect back to the other sheets the original row came from based upon the reference entered into column A. Perhaps this is done via a VLOOKUP command, but this is a very unfamiliar area for me.

    I have attached the workbook for analysis.

    I need the VBA code to do this, if possible maybe a alternative to vba - As I do not know how to combine more than 1 vba code.
    If someone is willing, they can adjust the code and attach it back for me?

    Your help will be greatly appreciated.

    Thanx
    JEDEMEYER1
    Attached Files Attached Files

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Goeie more jedemeyer1,

    Maybe the attached WorkBook is what you are after.

    Also see the "Please Consider" note at the bottom of this post.

    Baie dankie.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Hi Winon,

    Thanks for the reply. But it is still not as it should be. Columns K - N should update from "BUYER +" to the other sheets. Not the other way around...

    All that was done now was the columns updated from the sheets to the "BUYER +" sheet was extended by 4 columns... (saw your little code edit :-))

    Baie Dankie. Wardeer die moeite sover.

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Hi jedemeyer1,

    Sorry for the mistake foutjie,

    Perhaps this is done via a VLOOKUP command,
    Instead of Vlookup, I have used Index Match as per the revised attached WorkBook.

    Hoop dis nou reg vir jou.

    Keep on Excelling!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Werk soos 'n Bom Winon,

    BUT, I need the reference cell to be the A column. I believe (I could be wrong) that you set the criteria cell to be the J column?
    That will cause a problem, as there can be duplicates in the J column. A column however will always be unique.

    Also, I see that if anyone should enter something into columns K-N on the other sheets (NOT BUYER +), it messes up the entire formula.

    Is there a way of preventing them to enter any data there? If possible, we can then insert their own note area where they can put their notes just before K, thus making L-O the unchangeable columns.

    Hoop jy verstaan wat ek probeer verduidelik.

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Hello jedemeyer1,

    Jy skuld my 'n Coke en 'n Bun!

    Sorry for the late response, but here you go.

    Geniet.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Baie Baie dankie,

    Werk wonderlik. Maar, kan ek jou nog eenkeer pla?

    I see a problem that can be caused if the user on the the sheets other than "BUYER +" should delete a line.

    Would it be possible, that if a row gets removed or deleted - that it will also remove the entire row details on the "BUYER +" side. FROM A - O

    You see, currently you can remove all the data of a already created row on the sheets, and the data I put in on the "BUYER +" sheet will still show on that sheet. They will then re-enter the row, but it will have the wrong values associated to it on columns L-O.

    Help my hier.. En ek stuur vir jou 'n biltong ruiker...

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Dit is 'n TAAI TOFFIE!!!,

    I really cannot tink of a way as to how to accomplish your last request.

    In a way it defeats the objective of your project design from the outset and how it has evolved from there. At this stage it seems to have reached the point of deminishing return, and may require a total new evaluation of your actual requirement/s.

    Maybe if we add Protection to the Sheets, and allow users to only delete from columns A through K, then in the Code for BUYER+, clear the contents in Range A to K first, and see what happens.

    Let me think this one through a little bit more, and I shall let you know.

    Mooi bly vir eers!

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    5 ure later en baie toets!

    Would it be possible, that if a row gets removed or deleted - that it will also remove the entire row details on the "BUYER +" side. FROM A - O
    Done! Check out the attached WorkBook.

    You may want to assign all the other Sheets with the same unique PassWord as well. For now the PassWord is "test".

    Geniet!
    Attached Files Attached Files
    Last edited by Winon; 03-24-2013 at 05:17 PM.

  10. #10
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Thanks / Dankie.

    Baie dankie vir die moeite.

    The workbook works better, yes. But it still has a problem. They can still erase (clear data) from all fields, this marks the row empty on the BUYER sheet, thus if they create a new row anywhere, that row replaces the blank one in the BUYER sheet. I know, I know... I like things perfect .

    However, I did my own research as well (Google, my sentimental friend). And got another way to avoid them deleting all the data.

    So I basically locked the first field after entry. This makes sure that if the row is made on the BUYER sheet, it will always refer to that unique reference in Column A. And the row cannot be deleted!

    I have attached it for you, for analysis. Maybe you can see something I might have missed. Password: EMDEN

    Thank You.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    More More,

    Nog iets kleins wat jy vir my kan doen.

    I need to add just 2 more columns after A. That will make N-Q the unchangeable columns, A-M the one's that update on BUYER sheet.

    Could you please add those, in a way that it does not affect anything else on the workbook.
    I have tried, but I really cannot.

    Dankie / Thanks

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    More jedemeyer1,

    Please could you fill your uploaded WorkBook with some basic Data as per your requirement, and send it to me again?

    Baie dankie.
    Last edited by Winon; 03-25-2013 at 04:47 AM. Reason: Spelling!

  13. #13
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Ok, find attached.

    I have deleted the other rows for now. I can always duplicate the sheets as necessary.

    I added 2 columns to "BUYER +" sheet and the sheet next to it "BD"

    As you will see, currently the data is not showing properly. I could adjust my VBA code to show until column 13 on the "BUYER+" sheet.
    But the Indexing formula is now taking the incorrect data back to the sheets.

    Example (N2 on Sheet "BD")

    The current code is:

    Please Login or Register  to view this content.
    I think you should change it to:

    Please Login or Register  to view this content.
    But I am not able to change this, I don't know how you blocked access, but your a master at that trade.

    Baie Dankie
    Attached Files Attached Files

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    I can always duplicate the sheets as necessary.
    If you duplicate Sheets, you will have to adjust the Sheet references in the Formulas in Columns N to Q accordingly. I have removed the "Block" in those Columns.

    When activating Buyer+ all Sheets other than Buyer+ should also save automatically, allowing for Data input in empty rows only from Column A to M.

    Besides that, I am pretty certain that you should find everything else now to be to your liking.

    Also see the "Please consider" note at the bottom of this post

    Happy Coding!

    Groete,

    Tot later.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    03-01-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Thanks / Dankie

    Had a quick once over, and it seems to work as it should. Like the way the formula is perfectly hidden now when the sheet is locked (nice touch).

    Baie dankie, alle vrolikheid vorentoe, en geniet die Paas naweek.

    Regards / Groete

    JEDEMEYER1

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    Baie dankie, alle vrolikheid vorentoe, en geniet die Paas naweek.
    Sonder my biltong?

    You are most welcome, glad I could help in some way.

    Thanks for the Rep.

    Help graag waar ek kan.

    Keep in touch.

  17. #17
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Copy data from column to other sheets, based upon vlookup/criteria on column a

    If you found another problem with the solutions offered to you in this Thread, you may go to your first post, and under Thread Tools, select "Un-Solved", and post your new question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1