Macros, lookup and if?

  • Hello

    I currently have the following issue which I would appreciate if someone could help me answer! I currently use two spreadsheets at the same time, one that I use all the time (my 'main' sheet) and one that I detach from an email. Basically, on the sheet that gets sent I am interested in only two columns - the refernce and the status (call these columns C and G). I have to look up all the refernces, and dependent on the status amend cells in my 'main' sheet accordingly. I therefore presume I have to do some kind of lookup reference from the sent sheet into the mainr, and then use the 'if' command to amend where appropriate. the only other headaches is that certain status means I have to change 6 cells in my main spreadsheet (though the same text in each cells - bar the date in part of one cell), and some status' means that I cannot alter my main spreadsheet. Is this possible?

    Apologies if this mail is not coherent!

  • Re: Macros, lookup and if?

    Sorry, but does anyone have any code that I could use? I am keen to learn, but wont be able to write it by myself.

  • Re: Macros, lookup and if?

    Sorry, Im not explaining myself well! :nono:

    I thought a macro would be needed because if I have say 1000 references to 'lookup' then a formuale wouldnt be able to do it. For instance,

    Sent Sheet
    Column B Colomn E
    REF Status
    12345 Matched
    23456 Matched
    34567 Unmatched
    abcde Matched
    45678 Wrong details

    I then need to 'lookup' all these references in my main spreadsheet and amend/leave alone according to status in the sent sheet. Using the above example, i currently have to amend all the matched status' that exist in my main spreadsheet (hence abcde doesnt exist in my main spreadsheet - so I would want the macro to simply ignore this and move to the next one). So on my spreadheet the above trades would be looked up using the references, and return the following values for anything that is matched and can be found in the main spreadsheet

    Col Y - Col Z - Col AA - Col AB - Col AC

    MA - SFT - Now Matched (todays date) - Mike - (todays date)

    Am I making more sense of what Im trying to do? Apologies if not!

  • Re: Macros, lookup and if?

    Ok I have tried to make it as simple as possible - in reality not all refernces are for me, and its definately not in the same order of refernce! Please bear in mind that I have two spreadsheets open, not within the same book but I think you should be able to get the drift. I have marked purple the cells that i manually look at between the two files, and the third sheet is what it should like after I have finished. Hopefully this helps you help me!!! :thanx:

  • Re: Macros, lookup and if?

    so your basically taking all the main sheet info, and adding a little extra info to it (rows Z thru AC). and you'd like that info to be added automatically?

  • Re: Macros, lookup and if?

    Yes basically. All the info that i get from the sent workbook, is used to update those references in my main workbook

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!