HELP! Worksheet Problem

Hi

I have a workbook containing 6 worksheets being Nov 01, 
Feb 02, May 02, Aug 02, Nov 02 and Jan 03.  

Each worksheet contains details of loans on these dates to 
clients.  Each client has a unique ID number and is in its 
own row with columns A-I being ID, Prefix, First Name 
through to post code etc and column J being the loan 
amount.

The first worksheet (Nov 01) contains 76 clients, the 
second (Feb 02) contains 189, (May 02) = 297, Aug 02 = 
345, Nov 02 = 342 and the last (Jan 03) contains 350 
clients.

I need a way to import all the details of the clients from 
each worksheet to a consolidated worksheet.  So if all the 
clients in November 01 are also in Feb 02 & May 02, I 
would like the consolidated sheet to show Empl Id in 
Column A, Prefix Col B, First Name Col C, etc through to 
postcode in Col I as in each worksheet. I would then like 
to be able to put each loan in the following rows starting 
at column J going in worksheet order.  For example Col J 
would be November 01, Column K could be loan amount for 
Feb 02, Col L would be May 02 etc.

Any help would be greatly appreciated.

Many thanks

Malcolm


0
malycom1 (9)
8/8/2003 10:54:32 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
635 Views

Similar Articles

[PageSpeed] 34

Hi

At start, it'll be a good idea to have a sheet p.e. Clients with columns ID,
Prefix, FirstName, etc, and define column ID and whole table (both without
header row) as dynamic named ranges (p.e. ID and Clients). In all your
monthly sheets you can then format ID column as Data.Validation.List with
source=ID, and in columns B through I you get client data through VLOOKUP,
like:
=IF($A2="","",VLOOKUP($A2,Clients,2,FALSE))

On Consolidation sheet, you enter ino cell A1 the formula:
=(IF(Clients!$A2="","",Clients!A2), and then copy it to range p.e. A2:I500
(you can have more rows to have the formula ready when you entre a new
client)

Into cell J2 enter the formula:
=IF(A2="","",SUMPRODUCT((Nov01!A2:A77)*(Nov01!J2:J77)))
Into Cell K2:
=IF(A2="","",SUMPRODUCT((Feb02!A2:A77)*(Feb02!J2:J77)))
etc

Copy cells J2:?? down for same number of rows, as you have links in columns
A:I


Arvi Laanemets


"Malcolm" <malycom@hotmail.com> wrote in message
news:088d01c35d9b$73129ea0$a401280a@phx.gbl...
> Hi
>
> I have a workbook containing 6 worksheets being Nov 01,
> Feb 02, May 02, Aug 02, Nov 02 and Jan 03.
>
> Each worksheet contains details of loans on these dates to
> clients.  Each client has a unique ID number and is in its
> own row with columns A-I being ID, Prefix, First Name
> through to post code etc and column J being the loan
> amount.
>
> The first worksheet (Nov 01) contains 76 clients, the
> second (Feb 02) contains 189, (May 02) = 297, Aug 02 =
> 345, Nov 02 = 342 and the last (Jan 03) contains 350
> clients.
>
> I need a way to import all the details of the clients from
> each worksheet to a consolidated worksheet.  So if all the
> clients in November 01 are also in Feb 02 & May 02, I
> would like the consolidated sheet to show Empl Id in
> Column A, Prefix Col B, First Name Col C, etc through to
> postcode in Col I as in each worksheet. I would then like
> to be able to put each loan in the following rows starting
> at column J going in worksheet order.  For example Col J
> would be November 01, Column K could be loan amount for
> Feb 02, Col L would be May 02 etc.
>
> Any help would be greatly appreciated.
>
> Many thanks
>
> Malcolm
>
>


0
arvi (37)
8/8/2003 12:12:34 PM
Reply:

Similar Artilces:

sumifs help
I have the following formula. =SUMIFS(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it? Use Countif instead of Sumif from =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) to =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this th...

Reports problem 04-11-05
I have run into this occasionaly where the reports seem to take a while to initaly load, sometimes they dont load at all. For example I will click on an activity report and sometimes after about 2 minutes it will load the inital unfiltered reports. Sometimes that same report may not load at all. It seems to happen too often to just be a little glitch. Is this normal? I've seen this problem too,.....its probably why Crystal is being dropped as CRM's reporting service. Its just very resource instensive...........anytime that happens, go into the CRM server, load the Crystal M...

Search a string in all worksheets ?
I am searching for a string say ABC in all worksheets in Excel 97. However, it seems that it only allows me to search it in the current worksheet. Is there any suggestion ? Hi Peter............ In XL2k, I just highlighted all three sheets using Right-click on one tab and choosing Select all sheets..........then did Edit > Find > and typed my search string in the window and hit "Find next" repeatedly and it cycled through all three sheets finding the string...........is that what you're wanting to do? Vaya con Dios, Chuck, CABGx3 "Peter" <anonymous@di...

Help !
I need to create a data input screen on excel where multiple users at the same time will use them & input data. This data then needs to be stored as a database as well, where i can use it to understand trends Thank you. and the question is ...? <abrahamsaj@gmail.com> wrote in message news:1132155054.927936.191640@z14g2000cwz.googlegroups.com... >I need to create a data input screen on excel where multiple users > at the same time will use them & input data. > This data then needs to be stored as a database as well, where i can > use it to understand trends >...

multi-thread problem
I am not sure how to correctly write this Worker Thread example program. And I realize that this specific problem is somewhat more of a C++ issue. But I really needed to make sure that I was correctly writing this part of the program. I've also encountered another problem, but I thought it could be connected with this first problem here. However, I would hoped to ask about it later. How do you write the statement that "post" the long integer in the Controller thread to the receiving function. UINT CThreadTestDlg::Thread1(LPVOID lParam) { CThreadTestDlg* pHwnd = (...

Help with this thing
It was working in the window "Transactions >> Sales >> Transactions of Sales", but exactly were publishing a Quotation, which i wanna print, more nevertheless was shut up to me network, then I closed the window and it threw several messages to me of error, and from that then it was blocked the quotation that was working. My question is as I can unblock this document? ...

ole drag and drop problem in a ListView
I've implemented ole drag and drop in my application's listview. My understanding is that I should get scrolling for free but I don't get any scrolling action. Am I missing something here? I'm new to OLE so any pointers would be greatly appreciated. Regards, Omundu. ...

Help with Outlook Setup!!!
I have XP Professional installed and Office XP Professional. I have several users setup for kids, as well as my admin account. I want to setup a Limited Account in addition to the Admin account for myself for daily use. Am I just suppose to pick a different account name and login password and then setup my MSN Messenger and Outlook POP email with same username and password that I used to setup the Admin account? I don't need to share any contacts or anything, I just setup the admin email in order to setup Outlook. I want to input my contact info etc., into the Limited account and use ...

Need function to insert worksheet name into a cell of that worksheet.
I have found a formula on the web help site to automatically insert a worksheet name into a cell of the worksheet. I have several worksheets and wish to identify each worksheet automatically in a cell of each worksheet. When I use the formula it initially brings in the correct worksheet name. However when I select another worksheet the prior name appears in the selected cell. Perhaps I need to change something in my formula or this is a problem that I can't correct. What is your suggestion? The following formula is what I am using in each worksheet. =MID(CELL("fil...

Problem copying table data as a picture onto chartsheet
I'm having a strange problem. I'm creating a special chart which has a pie chart on it as well as some tabular data that is formatted the way we want it on a worksheet, then pasted onto the chartsheet as a picture. (The pie chart aspect is working fine. I'm just having a problem with the tabular data.) When I copy the tabular data manually by selecting the data on the worksheet, then going to the Home tab and choosing the Paste menu dropdown, then "As Picture", then "Copy as picture...". In the resulting dialog box, I choose "As shown on scr...

Auto Archive problem
Hi All, We use Outlook 2003 Sp3. One of my users has this problem with the Auto Archive feature: certain folders of his mailbox don't get archived. The emails stay in these folders and others folders emails get moved to the PST file according to the Auto Archive settings. Even if I try to archive manually some of the forlers that don't work, nothing gets archived. What can I do with this? Regards, Are you aware that the Date used to archive is the Date Last Modified? This may be causing what you think is unsual behaviour. When were the items last modified? Regards Judy ...

HELP !!! I have a ARRAY Formula HELP !!!
Hello, Here is the ARRAY Formula I have and this is what I am using it for. The situation is that it worked 1 time and than not again. =INDEX(D48:K48,,MAX(IF(D48:K48<>"",COLUMN(D48:K48)))-COLUMN(D48)+1 Duty: I have a row of number that appear hourly (DOLLAR AMOUNTS), the numbe are anything from nothing to 10000. I want the hourly number to appea in specified cell. Here is an example. (I am using EXCEL 2000) Row D48:K48 answer in cell G2 1st hour D48 = $100.00 G2 Should be $100.00 2nd Hour D48 = $100.00 E48 = (nothing) G2 Should be (nothing) 3rd Hour D48 = $1...

exch 5.5 help
I am in a progress to upgrade Exchange 5.5 (on NT4) to Exhange 2k3 (on 2k3). I setup a test machine and upgrade the OS to w2k3. 1st I want to connect the 5.5 to AD, so I should install ADC. Can anyone tell me the steps? Frorestprep, domainprep, setup adc, and upgrade to exchange 2k3? If you run through the steps in the E2K3 deployment tools they will walk you through everything. -- Hope that helps. ------------------------- Jaclynn Hiranaka Enterprise Messaging Support This posting is provided "AS IS" with no warranties, and confers no rights. � 2004 Microsoft Corporation. Al...

Problems sharing a file
I'm running Excel 97 SR2 and Win98SE. When I share the workbook Excel wants to save the book. OK. It then notifies me that it contains macros & VB code ... OK. I then get an illegal operation message and it closes Excel without saving. When I open the file again, sharing is not enabled. Did I miss something? Anyone have any ideas on what is wrong? Oscar ...

Help with Formula Please 02-19-10
Need a Formula for the following: Data Table A B C D E F G H I 1 Tom A W 2 H 30 84 30 2 Peter A W 3 H 3 Nick B L 1 A 70 Columns F1:I3 from Data Table has break scores for each player. Below is the Result Table where I need to show a summary report for high breaks. I have no problem with Break as I use the Large function. I need a formula to insert in A1 and A2 to place the name for the corresponding breaks below. Result Table High Breaks A B Name Break 1 ...

Need help in data copying.
Hi I have an invoicing file in excel (Sheet1). I need to store the dat which is invoiced into another sheet. My Invoice Data starting from Ro 8 and column B to F (The first item is from B8-F8, second item i B9-F9). B-Item Code, C-Item Name, D-Qty, E-Price, F-Total. Once I print the invoice, I need to transfer the data to another shee (Sheet2) . When I create another invoice, the new data should be added below t the previous data in Sheet2. So that I can have all the items I sol in Sheet2. Can someone help me sending a macro for it??? I will be grateful to you. Thanks in advance Tom -...

help...help...help
I just installed Microsoft Office XP Professionaql with no problem. However, whenever I try to perform any task such as opening contacts area in order to create an entry, I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Other information that might be important is that I use a pst file. The error message also occurs when I try to open the Contact folder from the folder list as well as when I try to perform any function. It was a clean install not an upgrade. Could you ...

OnActivateView problem
I have a class derived from CView, say CViewA. I have controls in a CDialogBar. when the view CViewA is activated I enable the button on the dialogbar and when it is deactivated I disable the buttons on the dialog bar. But i donot want the buttons to enable or disable when there is switch between same type of view. This is creating refreshing problem. First the CViewA, OnActivateView is called with bActivate equal to False, then the same function is called with bActivate equals to true. I used the following code to find which view is getting active:- CMainFrame *frame = (CMainFrame *)AfxGet...

email a worksheet
Is there a way to email, just one worksheet froma workbook, don't want to send entire workbook, right now have to do alot of copy/paste into an email with just content want. Excel 2007/Outlook 2007 on Vista enterprise HTH: http://www.rondebruin.nl/mail/folder2/mail3.htm Micky "Nadine" wrote: > Is there a way to email, just one worksheet froma workbook, don't want to > send entire workbook, > right now have to do alot of copy/paste into an email with just content want. > > Excel 2007/Outlook 2007 on Vista enterprise ...

Macro Help
Hi, I had alot of help yesterday from Jacob with the following macro, but am getting a 'run time error 13' when trying to run the macro, and i cannot see why. Any help much appreciated Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row If Range("E" & lngRow) = "" Then Set objApp = objOL.CreateItem(1) With objApp ..Subject = "Change Password for system" & Range("A" & lngRow)...

help me #2
how to restrict entering of same values or data in excel cell Hi if you mean the 'preventing of duplicate entries' you may check the following site http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Frank Kabel Frankfurt, Germany mangesh khati wrote: > how to restrict entering of same values or data in excel > cell ...

Problem with LeadTools CreateWindow inCFormView -Help !!!
Hi , I have been using LeadTools in Visual Studio 2005. I have a tabctrl and dialogs in each tab and developed using CFormView. So a tabCtrl is a child of CFromView and tab1 is a child of TabCtrl. I want to insert a LEAD control in one of the tabs .i.e, Dialog.I am unable to insert a leadcontrol but inserting a control in the view was easy.The problem is I am not able to get the HWND associated to a particular dialog which is super child of View. Can somebody help me in creating a lead control in the dialog configdlg .h LAnnotationWindow m_LAnnoWnd; FormView.cpp CMyTabCtrl m_myCtrlTab; ...

HelpProvider and HTML Help interaction
I have an application with a .chm help-file. But I have some questions about the behaviour of the help-window. I use a modal application window and I can start the help. The help windows appears but it is allways in foreground of my application window. I can set the input focus on my window, the help window becomes inactive but I cannot move my window over the help window. So I have to close the help window or minimize it or move it aside of my application window when I want to go on in my application. The other problem is when I minimize the help window and then open a dialog...

CString help
I'm looking at a website on CString Management: http://www.codeproject.com:80/string/cstringmgmt.asp In the section entitled, "CString to char * II: Using GetBuffer," the author stresses calling ReleaseBuffer after calling GetBuffer. Is this always necessary? I often use CString::GetBuffer when using CStrings in MessageBox dialogs like so: MessageBox(m_hWnd, cString.GetBuffer(0), lpTitle, MB_OK); Should I be adding a ReleaseBuffer after a MessageBox call? Should I be passing my string data to the MessageBox in another way? Regards, Joe > Is this always necessary?...

Exchange 5.5 Problem
I can't send email to this email address and I know it works since I tested it using my yahoo and hotmail accounts. For some reason our exchange 5.5 server is not sending it out. This is the message I get in return. "Your message did not reach some or all of the intended recipients. Subject: test email Sent: 1/6/2004 10:25am The following recipient(s) could not be reached: 'frednezzar@club-internet.fr' on 1/6/2004 10:25am The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ; p=hmt;l=londondc-04010616244...