We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Linking 2 Excel Spreadsheets

Options
Hi All,

I have a column within a spreadsheet (profile spreadsheet) that has a list of names within it and I have a second spreadsheet (reconcilliation spreadsheet) that has a column on it via which I manually input a list of names. What I want within the column of the reconcillation spreadsheet is for it to flag any names that are missing from this spreadsheet that are on the profile spreadsheet. Is this possible to do and if so how?

Thanks in advance

Comments

  • GunJack
    GunJack Posts: 11,829 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    not quite sure what you're trying to achieve, but yes linking 2 s.sheets in such a manner (if I'm reading your requirement correctly) is perfectly normal.....you just need to sort the logic of your requirement out, and be prepared for it to take a few steps to do :) It will involve formulas/lookups/etc in one sheet that relate to the other sheet's data.
    ......Gettin' There, Wherever There is......

    I have a dodgy "i" key, so ignore spelling errors due to "i" issues, ...I blame Apple :D
  • shaz77_2
    shaz77_2 Posts: 1,881 Forumite
    I probably didn't explain myself very well, what I'm looking to do is to input a list of names into column G of the reconcilliation spreadsheet and within this column I want it to return an error message if names are missing from it compared with the same column within the pofile's spreadsheet.
  • GunJack
    GunJack Posts: 11,829 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    vlookup on the first sheet, looking at the name, and checking off against the second sheet...the error will be shown on the first sheet, i.e. this name's on this list but not on the reconciliation sheet :)

    You could just use 2 tabs in one s.sheet for this, unless there's other factors you've not mentioned....
    ......Gettin' There, Wherever There is......

    I have a dodgy "i" key, so ignore spelling errors due to "i" issues, ...I blame Apple :D
  • AlwaysSomething
    AlwaysSomething Posts: 240 Forumite
    Part of the Furniture 100 Posts
    edited 26 October 2012 at 1:01PM
    Excel2.png

    I think the picture describes your problem and a solution. I know you have two sheets. We will come to that later.
    Column A is a list of names you want to reconcile.
    Column G is your profile list - all the names.
    Look at Column H. It tells you for each name in Column G, how many times if can be found in column A. Notice this would also warn you if you had a name twice in column A.
    Column J shows you the formula used in Column H. It uses the countif() function. First argument of countif is the range to look in. Second is the item to look for. Countif is clever, and can do more than just exact matches, but that's all we are looking for here. Countif tells you how many times it found G3 in the range A3 to A29.
    Be careful if you copy and paste this formula to the next row - it will now look for G4 in the range A4-A30. That's not what we want - we want the range to stay at A3-A29. That's what the $ signs do. A$3 : A$29
    So I think that solves your checking problem. Now we just have to make it work across two spreadsheets. I can suggest a couple of ways of doing this.
    1) Just do it all in one sheet as above. make a linked copy of the Profile sheet within the reconciliation sheet. Put this formula in cell G3
    ='C:\Users\Shaz77\Documents\[Profile.xlsx]Sheet1'!G3
    Now cell G3 in your recon sheet will have the same value in it as G3 in your Profile sheet. Copy and paste that all down column G - enough rows to make sure you get all your values across - doesn't matter if you have too many rows - they will just copy over blank cells. Note that you don't have to reference G3 to G3 - you can put it anywhere convenient.
    2) You do the check within the profile spreadsheet. You make column H in the profile sheet refer to column A or wherever in the reconciliation sheet. Here is what H3 in the profile sheet would look like:
    =countif('C:\Users\Shaz77\Documents\[Reconcile.xlsx]Sheet1'!A$3 : A$9999,G3)

    Hope this helps
  • shaz77 wrote: »
    I probably didn't explain myself very well, what I'm looking to do is to input a list of names into column G of the reconcilliation spreadsheet and within this column I want it to return an error message if names are missing from it compared with the same column within the pofile's spreadsheet.

    In column H of the reconciliation spreadsheet, enter
    =IF(ISERROR(VLOOKUP(g1,profile!g:g,1,FALSE)),"MISSING",VLOOKUP(g1,profile!g:g,1,FALSE))

    If you want something that calculates on the fly (i.e as you type something into the reconciliation spreadsheet it tells you if there's an error) then you're into VBA territory.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.8K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.8K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.8K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.