+ Reply to Thread
Results 1 to 3 of 3

Create array and formula based on 2 different sheets

  1. #1
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105

    Create array and formula based on 2 different sheets

    Hi everybody,

    A bit of a (from my perspective) complex query today, hopefully your fine selves will be able to make short work of this.

    I have 2 sheets which are populated from another program using an OLE link. That's all fine, no problems exist.

    The problem is, when I create the first sheet, the program will extract all data from my query, and the second will be a subset of that data.

    I have 5 columns of data, so I need the 6th column to be a formula telling me if it is a duplicate cell.

    I can do a Vlookup (=VLOOKUP(A2,'Results (Urgent)'!$A$2:$F$15,1,FALSE)) which tells me if the data exists in my second sheet, but I have to do it by hand. (E.g. Once the data is in, I know how big my dataset is.)

    What I would like to do, is once my second sheet has been populated, create the Vlookup based on A) the size of my dataset in Results (Urgent), and B) copy the formula into each cell in my Results.

    The only way I can think of doing this would be to create a For...Next, based on the size of results (For x = 1 to "Last Cell in Results") and then create a Vlookup based on the size of Results (Urgent) (ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Results (Urgent)'!RC[-6]:R[ Last Cell in Results(Urgent) ]C[-1],1,FALSE)")

    Sorry for the long ramble, I'm just trying to get every bit of data into this post so as to avoid confusion.

    The question at the end of the day: How can I programatically insert a check which tells me if data exists in a sheet, based on varying datasets in 2 sheets?

    Thank you for your assistance,

    Bob

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Bob

    Couldn't tell what your second sheet was called, so assumed it was called results.

    Please Login or Register  to view this content.
    rylo

  3. #3
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Hi rylo,

    Thanks for that, worked perfectly. You were correct, my sheet was called Results, apologies for not specifying this.

    As it happens, I had put together a workaround using 2 While statements to count each cell 1 by 1, which took twice as much code and would take a lot longer with more values.

    Thank you again, this was a good excercise for teaching me how to write Range formulas.

    -Bob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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