Automatic update of information in a spreadsheet

Hi,
I am using Microsoft Excel 2000. I have just done a spreadsheet which 
contains information about patients. In one worksheet I have the raw data and 
then in others I have copied columns across and then used IF(AND) statements 
to abstract the information I need, e.g. to work out how many males there are 
in each of the hospitals. What I need to know is how do I set it up so that 
when a new entry is added to the raw data, i.e. another patient in a new row, 
it automatically updates the information in the other worksheets? What is 
happening at the moment is when I add another row the IF statements only work 
up to the cell that I put the formula in, so I have to go through every 
worksheet and drag down the formula into the new cells in every column, which 
takes a lot of time. Can you please help me?
0
aolo7276 (1)
4/12/2005 5:50:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
437 Views

Similar Articles

[PageSpeed] 1

Excel doesn't push data to other worksheets very well, but it can pull data very
nicely (with =sheet1!a1 type formulas).

But it kind of sounds like you're doing summary statistics based on information
in your main data table.

I find it much easier to keep all my data together on one sheet and then
manipulate that the way I want.

You may want to look into pivottables based on dynamic ranges.

For information about dynamic ranges, visit Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic
(the range the name refers to can expand/contract whenever you add/delete an
entry)

And for pivottables:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

aolo7276 wrote:
> 
> Hi,
> I am using Microsoft Excel 2000. I have just done a spreadsheet which
> contains information about patients. In one worksheet I have the raw data and
> then in others I have copied columns across and then used IF(AND) statements
> to abstract the information I need, e.g. to work out how many males there are
> in each of the hospitals. What I need to know is how do I set it up so that
> when a new entry is added to the raw data, i.e. another patient in a new row,
> it automatically updates the information in the other worksheets? What is
> happening at the moment is when I add another row the IF statements only work
> up to the cell that I put the formula in, so I have to go through every
> worksheet and drag down the formula into the new cells in every column, which
> takes a lot of time. Can you please help me?

-- 

Dave Peterson
0
ec357201 (5290)
4/12/2005 1:39:57 PM
Reply:

Similar Artilces:

issue with serial port on module, updating form
Hello, Ive created a serial port instance on a module. In the same module I have the sub data_received. Now I'd like to update a control in a form, how can I do it? Thanks in advance You need to use Control.BeginInvoke or .Invoke to update the UI via a Delegate. You can download the VB2005 Terminal example from my website, which illustrates this. Dick -- Richard Grier, Consultant, Hard & Software 12962 West Louisiana Avenue Lakewood, CO 80228 303-986-2179 (voice) Homepage: www.hardandsoftware.net Author of Visual Basic Programmer's Guide to Serial Comm...

Bottom of spreadsheet hidden in full screen view
If you change Excel to Full Screen View and your taskbar is always on top (as it normally is), the bottom of the spreadsheet is hidden behind the taskbar. Does anyone know how to get around this without hiding the taskbar? I have found that if I drag the taskbar to the bottom and then back up again, the Excel window resizes itself correctly and the bottom of the spreadsheet is visible again. Problem is, I want to do this from VBA! Can anyone help please??? Bringing this post back to the top because it is getting lost in the depths of the forum. I've searched the whole Excel fo...

Can I open a spreadsheet If I don't have excel
My friend sent me a spreadsheet....but I dont have excel. How can I open it. thanks sincerely, Dottie Hi Dorothy, If you do not have Excel, you can download an Excel viewer from Microsoft. The viewer will allow you to read but not edit a workbook.The viewer is freely downloadable from: http://tinyurl.com/54tzl Another possibility would be to download the freely available OpenOffice suite which can read and write Excel workbooks. The download is > 60MB (IIRC), but the suite can also be obtained on CD. For OpenOffice go to: http://www.openoffice.org/ A third ...

Look up values and place in spreadsheet
I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. Try this *array* formula in A2 of Sheet1: =INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$A$1,ROW($A$1:$A$30))...

Can't separate spreadsheets
Using Excel 2000, I've suddenly lost the ability to have different spreadsheets appear in different windows (ie, to open Excel in different windows) which I can move around the screen. They all replace each other in the same window. I can toggle between them using the list in the "Windows" drop-down menu, but I can't have them side-by-side on the screen. Even the "tile" command doesn't work. Can someone suggest how to fix this? Bobbi Maybe... Tools|Options|View Tab|Check "windows in taskbar" Multiple windows, but a single instance of excel, right...

I need to get week start and week end dates to change automatically
I'm trying to get the week start and week end dates to chang automatically on a time sheet that I am working on. Anybody have formula? Joh -- Message posted from http://www.ExcelForum.com John, =INT(NOW()+2-WEEKDAY(NOW())) will return Monday's date, and =INT(NOW()+6-WEEKDAY(NOW())) will return Friday's date. Format as dates. HTH, Bernie MS Excel MVP "jlyons360 >" <<jlyons360.192pxt@excelforum-nospam.com> wrote in message news:jlyons360.192pxt@excelforum-nospam.com... > I'm trying to get the week start and week end dates to change > automa...

Automatic Replies and Forwards..
Automatic replies and automatic forwards to Internet recipients are disabled in Exchange 2000 Server and in Exchange Server 2003. What is the argument for keeping this default? What are the security risks specifically? Any advice or suggestions on this would be greatly appreicated. Thanks, Dana .... you're letting spammers know your email addresses are valid, mail loops... (auto-reply to internet recipient generates an auto-reply to which you auto-reply again... anon) -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ---------------------------------------...

create a spreadsheet from another app
Hi, I am creating Excel spreadsheets from our application. We start Excel and then send DDE commands to it to create spreadsheets. Is there a better way of doing this that is not so error prone ? Excel seems to have DDE problems often and there seems to be some mysterious re-entrency problems where both our application and Excel will have be restarted in order to get the communication flowing again. Sincerely, Lynn McGuire On Jun 26, 1:43=A0am, Lynn McGuire <l...@winsim.com> wrote: > Hi, > > I am creating Excel spreadsheets from our application. =A0We start > Excel and t...

Charts GONE after Windows XP SP3 Update!?!
I have Office 2007 SP1 Enterprise Edition and, until last night, Windows XP Professonal SP2 installed on my PC. Last night I went through the normal Microsoft Update site to upgrade to SP3 and everything seemed to go well. However, I noticed this morning that all of my Excel files I have that previously had worksheets on them showing charts are not displaying those charts at all. The worksheets that they were on are still in the files, but the charts themselves seem to have disappeared. Has this happened to anyone else and does anyone know of a way to resolve this? Thanks... Hi, ...

Disable "Quotes, News and other Information" in update now.
I'm using Money 2006 stand alone without passport.net. When in Money and choosing Connect to Bank/Update Now (update online information), I'm given two options by check marks: Quotes, News and other information and Account Transactions. I want to permanently disable the Quotes, News and other information category. If I uncheck the box, it always comes back the next time when I update. I want to uncheck the box and keep it unchecked so as to move forward with only the Account Transactions in a timely manner. Is there a place where I can make this permanent change? Thanks! -- Paulie.....

Automatic Files Saves
In previous versions of Excel it was possible to setup periodic automatic saves that would prompt the user to save. I am not looking for the auto-recovery save. Is there an add-in or update that can be installed to do the same thing in Version 2002. ...

Appending spreadsheets
Good morning I'd like to take about 16 Excel spreadsheets and merge them into one. They will combined still fit within the 65536 row limit and have all identical column/fields. Without outputting them to CSV and doing a DOS append/save command, and without copy/pasting them into a new workbook/sheet, do I have any other alternatives Thank you Derek Hi Derek >and without copy/pasting them into a new workbook/sheet What problem do you have with that?? Try http://www.rondebruin.nl/tips.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Derek Wittm...

links not updating
Hi I have two files. Data in File 1 is linked to data in file 2. I have both the files open. When I change a value in file 2, the data in file 1 should update automatically. This doesn't seem to be happening. I have to go to each cell and hit F2 for the update to happen. I've tried both F9 and Shift-F9. The calcuations are set to automatic but still it doesn't update uatomatically. Any idea what is happening? Thanks Utkarsh Do you have calculation set to automatic (tools|Options|calculation tab)? If yes, are you sure you have both workbooks open in the same instance of excel? ...

I have added FrontPage 2003 and now I can't update my Pub2003 site
Ever since I installed FP 2003, I have not been able to update my Publisher site. Does FP cause a known conflict? -- Dedicated Virtual Assistant at http://www.world-wide-virtual.us Hi Damzish (msgroupsreply@world-wide-virtual.us), in the newsgroups you posted: || Ever since I installed FP 2003, I have not been able to update my || Publisher site. || || Does FP cause a known conflict? There are no known issues. What appears to be the problem? What is happening? Are you getting any error messages? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ T...

adding numbers in an excell chart automatically
How to create an ongoing addition of numbers in an excell chart. Or sum of Numbers I should Say. I need the info for work. I think there is an easier way than how I am doing it. Worksheets are the things to use for adding numbers. That's not what a chart is designed to do. -- David Biddulph "sanicay" <sanicay@discussions.microsoft.com> wrote in message news:E94E1035-DC2C-4698-AC32-3439BDB2BDA6@microsoft.com... > How to create an ongoing addition of numbers in an excell chart. Or sum of > Numbers I should Say. I need the info for work. I think there is an >...

automatically deleting messages in the SPAM folder
In the Outlook Express 6 I am using, there are two separate folders named 1) Deleted items and 2) SpamMails. The messages in the Deleted messages get automatically deleted when O E 6 is closed. But the messages in the Spam Folder (even if deleted) are moved by O E 6 into the Deleted items Folder and since I want to see them removed from the system, I have to delete them, once again. Is there any way, O E 6 can be asked to automatically delete all the messages in the Spam folder? O E 6 also asks me every time it is closed if I want to compact all messages to save space bla bla,...

Updating Excel Spreadsheet
I have 3 users that use the same spreadsheet, is there a way of automatically updating the spreadsheet of one user with newer information input by another user. Hello, I think you should use shared workbook. Ex2003: Tools/Share Workbook... Ex2007: Review Ribbon/Share Workbook... Obviously, you need to save the file into shared drive. Let us know if it works :) -- Milan Bortel MCP, MCAS, MCTS GOPAS Computer Training Center Brno, Czech Republic "joesw" wrote: > I have 3 users that use the same spreadsheet, is there a way of automatically >...

help wanted in updating worksheet automatically
Could you please help us with an excel query. We have an excel file with fields in a random order. The values in these fields have to be picked up and arranged one below the other in a separate area. The values to be picked up always have a fixed cell address. Awaiting reply Elle How about just selecting the range, copy it, paste it to its new location. Then select that new range and do Data|Sort? Madhu wrote: > > Could you please help us with an excel query. > > We have an excel file with fields in a random order. > > The values in these fields have to be pic...

Free/Busy information only displayed from 1/1/05 to 3/1/05
Since the beginning of the year, the Free/Busy window for almost ALL of my (800+) users is 'stuck' at 1/1/05 through 3/1/05. Most users publish 2 month's worth of free/busy from Outlook, so I should see one month prior to today's date and two months ahead. If a user increases the # of months published in outlook, the free/busy time is adjusted to 1/1/05 through the correct month, so I know that _some_ level of communication is happening between outlook and exchange. I run a mixed exch5.5 and exch2003 environment. Most clients are outlook 2000 or 2003. The problem oc...

Updating Accounts
About a month ago I opened my Money program and saw that it had deleted the sign-on info I had saved for Home Depot and MBNA. Since then, it has not allowed me to set up online updates. I keep getting "Account Updates Not Available, Please Try Again Later". These accounts had been updating fine before this. I have called support, talked to support online, they closed the session every time as unresolved. WHAT'S THE DEAL? I am seriously looking into trashing Money and using Quicken! ...

Help working out hourly production rates in spreadsheet please.
Hi all, Stuck on this one now. In my spreadsheet i have 3 columns that contain the following info for my production machines Total shift time (this will usually be 7:45 or 8:30 - formatted in hh:mm format) Downtime per shift (any value upto max shift time - formatted in hh:mm format) Kgs produced. I would like to keep the time in hh:mm format as i do further calculations on these - % downtime, total downtime, total shift hours etc (and also cos it took be ages to figure out how to add up in time and i couldn't believe how simple it was when i figured it out) What i would like to be abl...

automatically update knowledge base from case files
CRM case files should auto add to the CRM Knowledge base. Without this function you are doing nothing but duplicating work. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=...

Looking for Work Load/Production Report Logs spreadsheet...
Hi, We are being asked to keep track of our daily work load. We need to record start and stop times as well as total time spent working on each job. It seems Excel would be perfect for this task because of it's Current Date and Time functions. Does anyone know whether this has already been invented and where I can find a sample Excel Worksheet? Or any assistance you can provide to make this mundane task less time consuming than writing it down manually is appreciated. Thanks in advance for your help. -Greg Hi Greg, The only way I can think of to do what you want is with an event drive...

Money 2002 Online Price Updates
I have recently updated my computer to VISTA and have had to re-instal Money2002 Personal and Business edition. Since then the online price updates no longer run on start up. I have to manually use the update price option and this does retrieve updated prices for the shares as expected. Does anyone kn ow how to fix the problem so that the update of prices happens automatically every 6 hours as it used to do when I was using Windos XP Home? Thanks in antcipation of getting a resolution to this problem. ...

Recipient policy updates fail with LDAP Error 8033
We noticed today that new user accounts were not getting their email addresses filled in as specified in the default recipient policy (Exchange 2003). I turned up logging on the server for MSExchangeAL and see that the server is failing to apply the policy because it gets an error #8033, Category LDAP Operations, Source MSExchangeAL -- Error text: LDAP search result on directory emsexch1.emscrm.com for entry 'C=emscrm,DC=com' was unsuccessful with error:[0xa] Referral [ 0000202B: RefErr: DSID-031006D9, data 0, 1 access points ref 1: 'com' -- The domain controller for both r...