Prev: delete columns in spreadsheet after data entered so it wont scroll
Next: Setting up a formula (when F2<11,000)=$25
From: Mr Fujisawa on 16 Mar 2010 10:39
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),calc!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.
From: Herbert Seidenberg on 16 Mar 2010 16:11
Excel 2007 Tables
Consolidate and Advanced Filter.
With macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_16_10a.xlsm

 | 
Pages: 1
Prev: delete columns in spreadsheet after data entered so it wont scroll
Next: Setting up a formula (when F2<11,000)=$25