Linking 2 Excel Spreadsheets

shaz77_2

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

GunJack

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.

shaz77_2

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

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....

AlwaysSomething

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

The_Almighty_Cucumber

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.