To Choose Vlookup Or Any Other Suitablle Function - Urgent

  • Thread starter Thread starter sayvari
  • Start date Start date
S

sayvari

Hi!

I have the debtors ledger data on excel sheets from 1995 to 2004,
need to construct an account for each of the Debtors over variou
years appearing in the excel sheets. The common link to all th
Individual Debtors is the FileNo.. There are 7 columns in all th
sheets namely - FILENO, DATE, DEBTORS NAME, RECEIPTSNO., AMT.RECD.
AMT.PAID., ONHANDBAL.

I need a function which can retrieve the related fields of informatio
with respect to each FILENO. onto the target sheet. The data on eac
sheet is anywhere between 10-20K rows.

Any suggestions are welcome.

Kindly explain, since I am not an advanced user.

Thanks in advance,

Sayvar
 
Hi
So you want a SUMIF accross multiple sheets. You may try the following

1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). e.g. you may use the following formula

=SUM((THREED('1995:2004'!A1:A1000)=File_number)*(THREED('1995:2004'!B1:
B1000)))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains your filenumber
 
Back
Top