If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help: vlookup from multiple sheets and spreadhseets
I currently have a spreadhseet which lists frauds on cards (card number,
date, description etc), 1 row for each card with 12 sheets, 1 sheet for each month of the year. I have another spreadsheet called 'at risk' with another list of card numbers. What it does is checks the fraud spreadsheet to see if the card is listed and, if so, pulls the date through and then the discription through. Obviously there are 12 sheets to check so I wasn't quite sure how to check 12 different sheets with just one formula. Currently the way i'm doing it is a total hack job. I have a sheet called 'calc' on the 'at risk' spreadhseet which basically replicates all the card numbers listed on sheet1 of the same spreadhseet (ie the list of at risk card numbers) in order to perform calculations on the data. On this calc sheet I have vlookups for each month of the year on the fraud spreadsheet, as follows =VLOOKUP(A15,'N:\FRAUD\[FRAUD Apr09-Mar10.xls]April'!$B$5:$O$114,12,FALSE) =VLOOKUP(A15,'N:\FRAUD\[FRAUD Apr09-Mar10.xls]May'!$B$5:$O$114,12,FALSE) Where column A is the card number (which istsellf is basically replicating coloum A from sheet1), and ths row contains Vlookups for all 12 months. This will then pull through any cells form the fraud spreadhseet which match. Then I have formulas to check each row for any data pulled through. because I can't nest more than 7 IFs i have to use two! =IF(ISNA(K15), IF(ISNA(M15), IF(ISNA(O15), IF(ISNA(Q15), IF(ISNA(S15), IF(ISNA(U15), IF(ISNA(W15),#N/A, W15), U15), S15), Q15), O15), M15), K15) and in the next column =IF(ISNA(L15), IF(ISNA(N15), IF(ISNA(P15), IF(ISNA(R15), IF(ISNA(T15), IF(ISNA(V15), IF(ISNA(X15),#N/A, X15), V15), T15), R15), P15), N15), L15) Now only one of these will have a value, so finally on sheet1 I have =IF(ISNA(calc!G15),IF(ISNA(calc!I15),0,calc!I15),c alc!G15) Which pulls the data through. This spreadsheet has some 5000+ rows, so combined with the fact I have 2 coloums on calc for each of the 12 months (as I want to pull 2 cells from the fraud spreadsheet for each match) this is quite a lot of data and very messy! So, is there a better way of doing this? Also, I will sortly have a second spreadsheet, FRAUD Apr10-Mar11.xls so whilst the current way works, I will soon have 2 spreadsheets to pull from! Any help would be appreciated. |
#2
|
|||
|
|||
Help: vlookup from multiple sheets and spreadhseets
Excel 2007 Tables
Consolidate and Advanced Filter. With macro. http://c0718892.cdn.cloudfiles.racks...03_16_10a.xlsm |
Thread Tools | |
Display Modes | |
|
|