multiple worksheet formula help

I have a 7 page workbook.  Page 1 is the "master" sheet containing formulated 
data from the other 6 sheets in the workbook.  Each of the 6 other sheets are 
set-up with the exact same rows and columns - they are simply assigned to a 
different sales representative.  I have the representatives "complete" a ROW 
on their sheet and "copy/paste" that row to the "master" sheet which then 
formulates the data via "sumproduct" formulas into charts.  My problem is 
that I have 6 representatives who can't even copy/paste without making errors!

What I need to do is find a formula that does the following:
  When a representative goes to one of the 6 worksheets, any ROW they 
complete will AUTOMATICALLY be COPIED over to the "master" sheet into an 
empty row.

Does such an animal exist?
0
kcip (2)
7/13/2006 6:38:01 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
389 Views

Similar Articles

[PageSpeed] 51

> Does such an animal exist?

Think this creature from my archives might just fit the cage here <g>:
http://savefile.com/files/6862951
Auto_summarize_n_stack_lines_from_12_primary_sheets.zip
(Full details inside)

As-is, it accommodates auto-copy from up to 12 "input" sheets (input range 
catered for is within A6:E130, viz: 125 rows x 5 cols per sheet) into a 
summary sheet, so you'd have a 100% buffer here (great provision for your 
future business expansion). Flexibility is provided to determine the desired 
stacking sequence from the 12 input sheets. And the creature will only stir 
and respond whenever F9 key is pressed - so you hold the control (Press F9 
only when recalc's needed to summarize from the 12 input sheets).
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kcip" wrote:
> I have a 7 page workbook.  Page 1 is the "master" sheet containing formulated 
> data from the other 6 sheets in the workbook.  Each of the 6 other sheets are 
> set-up with the exact same rows and columns - they are simply assigned to a 
> different sales representative.  I have the representatives "complete" a ROW 
> on their sheet and "copy/paste" that row to the "master" sheet which then 
> formulates the data via "sumproduct" formulas into charts.  My problem is 
> that I have 6 representatives who can't even copy/paste without making errors!
> 
> What I need to do is find a formula that does the following:
>   When a representative goes to one of the 6 worksheets, any ROW they 
> complete will AUTOMATICALLY be COPIED over to the "master" sheet into an 
> empty row.
> 
> Does such an animal exist?
0
demechanik (4694)
7/13/2006 8:31:02 AM
Thanks Max.  Somewhat overwhelming initially, but I will try to understand 
and apply.

"Max" wrote:

> > Does such an animal exist?
> 
> Think this creature from my archives might just fit the cage here <g>:
> http://savefile.com/files/6862951
> Auto_summarize_n_stack_lines_from_12_primary_sheets.zip
> (Full details inside)
> 
> As-is, it accommodates auto-copy from up to 12 "input" sheets (input range 
> catered for is within A6:E130, viz: 125 rows x 5 cols per sheet) into a 
> summary sheet, so you'd have a 100% buffer here (great provision for your 
> future business expansion). Flexibility is provided to determine the desired 
> stacking sequence from the 12 input sheets. And the creature will only stir 
> and respond whenever F9 key is pressed - so you hold the control (Press F9 
> only when recalc's needed to summarize from the 12 input sheets).
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "kcip" wrote:
> > I have a 7 page workbook.  Page 1 is the "master" sheet containing formulated 
> > data from the other 6 sheets in the workbook.  Each of the 6 other sheets are 
> > set-up with the exact same rows and columns - they are simply assigned to a 
> > different sales representative.  I have the representatives "complete" a ROW 
> > on their sheet and "copy/paste" that row to the "master" sheet which then 
> > formulates the data via "sumproduct" formulas into charts.  My problem is 
> > that I have 6 representatives who can't even copy/paste without making errors!
> > 
> > What I need to do is find a formula that does the following:
> >   When a representative goes to one of the 6 worksheets, any ROW they 
> > complete will AUTOMATICALLY be COPIED over to the "master" sheet into an 
> > empty row.
> > 
> > Does such an animal exist?
0
kcip (2)
7/13/2006 11:46:02 AM
"kcip" wrote:
> Thanks Max.  Somewhat overwhelming initially, but I will try to understand 
> and apply.

You're welcome !
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

0
demechanik (4694)
7/13/2006 2:53:02 PM
Reply:

Similar Artilces:

External Links and Multiple Instances
I am running Excel 2000 on Windows XP Professional. For a long time, I was able to open up files in separate windows and to create links between these files by typing "=" in a cell of the target sheet and then clicking on a cell in the source sheet. I recently installed a second monitor and video card on my computer. Since then, I have been unable to both open multiple instances and create links between those multiple instances. At first I was unable to create multiple instanced. I have managed to create multiple instances in three ways. First, I have gone to options - ge...

Orphaned Links -- HELP!
Mac PowerBook G4, 2 GB RAM running Excel 2004 I copied a range of cells into a a new worksheet in a workbook. Now I've got a mysterious link I don't want somewhere on the copied range. I cannot find any external links in the copied cells -- they are all numbers and text, no formulas. I've tried Edit|Links|Break Link but it doesn't work. I tried moving the new worksheet out of the old workbook. When I try to save the old workbook without the new worksheet, Excel asks me if I want to save the link to the new unnamed worksheet. I have the options of OK and Cancel. I hit Cancel,...

Need Help #9
Hi This =IF(H2,MIN(10,H2),0) returns the numbers from column H with a maximum of 10. The formula is returning 10 if the cell is 10 or over, is something wrong. Question: I need it to return 0 if 10 or over. Microsoft Excel 2002 Thanks in advance Dave On Mon, 14 Nov 2005 16:43:49 -0000, "Dave 2005" <dave@zillion.karoo.co.uk> wrote: >Hi > >This =IF(H2,MIN(10,H2),0) returns the numbers from column H with a maximum >of 10. > >The formula is returning 10 if the cell is 10 or over, is something wrong. > >Question: I need it to return 0 if 10 or ...

multiple forests
Hi, I'm trying to get OWA to work properly in a multiple-AD-forest Exchange 5.5 environment. Each forest has the proper trusts setup between the two, but for some reason, only one domain will succesfully authenticate to the OWA server. Is it possible to have one OWA server for all the forests, or do I need to setup one inside each forest to work properly? end of line, Reed Wiedower you will need a seperate OWA for each forest. "Reed M Wiedower" <district@gmail.com> wrote in message news:eKD4yxVXFHA.3188@TK2MSFTNGP09.phx.gbl... > Hi, I'm trying to get OWA...

badly need some help on CRM customization
Hi badly need some help on CRM customization. I need to add an additional account lookup field to the sold product. We sell software and we need to track end users (40% of buyers aren't the end users), so i want to be able to set an end user (to attach an account) to each product in the order. Yes I know that 1.2 doesn't support custom lookup fields but i need to transfer all of the data from our current customer database and i want it to be accessible from one interface. I wonder if i can create a field in the SalesOrderDetail table to store accountid (uniqueidentifier type), map i...

formula to replace cell with over 8 characters with word LAN
Hello Group, I hope someone will be able to help. I need a formula that can check each cell in column "D" and if it has over 8 characters in the cell, return a value of the word LAN in column "F". Anything with 8 or under should remain as is. There will be up to 600 row entries in column "D". example of col: 799Z 777X 79BM 08/30/06(An) 08/30/06(Put) 08/30/06(InT) 76CC I'm aware that with all the talent and expertise in the group, this is small potato's, but it is greatly appreciated. Thank you, Calop ?... =IF(LE...

Need help writing an update query
I'm not good at this so I need some help. Here's what I want to do. I have two tables with fields that need linked to add an update. To show what I want to do I have put the table and fields in brackets. Thanks. Here it is: If [Status Table:Status=Sold or No Bid] and [Status Table:Date=1/11/2008] then in [List Table:Publication Fee] enter [$30.00] On Tue, 22 Jan 2008 17:23:17 -0800, Rick <Rick@discussions.microsoft.com> wrote: >I'm not good at this so I need some help. Here's what I want to do. I have >two tables with fields that need linked to add a...

Adding Multiple Reports using SendObject
I need to send Multiple reports in a *.SNP Format. Can anyone guide me as to how this can be done? Thanks -- -The Novice Learn Today, Teach Tomorrow Great Success is ones ability to ask for Help. ...

publisher 2003 can't upload site Please Help
Greetings, I have tried to upload my 2003 publisher website for 4 hours. I know how to use ftp, I know how to use http due to the wonderful directions I found through this website. When I use FTP I get 'Publisher could not save the fiel to the Web. Check your URL or network connection'. I check them and they are fine. When I use HTTP the programs stops responding, the page goes white and this can be verified by ctrl alt delete and you can clearly see the writing not responding. After I get it shut down I get a error report. Error signature: szAppName: MSPUB.EXE szAppVer:...

How can I change all the figures on a worksheet by a set %, witho.
Hi taking a guess here as your question was truncated (always best to have a short subject and put the details in the message) - type the percentage that you want to increase your figures by (i.e. 110% to raise them by 10%) in a cell somewhere and copy it, then select all the cells to increase and choose edit / paste special / multiply and click ok hope this helps Cheers JulieD "magsgoode" <magsgoode@discussions.microsoft.com> wrote in message news:E83A105B-733C-4D99-9F2A-4989F406C412@microsoft.com... > ...

If the result of a formula is negative make equal to zero
I do have a worksheet that calculates sales profits or losses quarterly in 3 different locations. For each quarter I do have a formula that gives me the increase or decrease amounts during the last couple of years. For the total result I need a formula that converts or ignores the negative results. Any calculation result with a minus sign in the formula should be equal to zero, so it's not included in the final quaterly total. Could somebody help me with it? Thanks! =IF(yourformula<0,0,yourformula) Regards Trevor "Nrippe" <Nrippe@discussions.microsoft.com> wro...

Need help please
Hey all, I am running Outlook that came with Office XP - 2002 I believe? Anyway, yesterday, I installed a new motherboard, and ended up having to reformat and install XP Pro SP2 clean. Thought I would get away with just a repair install of XP, as this has worked for me in the past, did not this time, so stupidly I was not as prepared as I would have liked to been. I have a backup, image that is, saved on an external usb drive with Drive Image 7. It is my hope, to be able to get to my Outlook folder, using Image Explorer built into Drive Image. I have reinstalled Outlook from Office XP...

*HELP* Winmail.dat Attachments Wont Go Away
Hello-- Our EVP is trying to email to our legal council and a couple of our BODs (all of which have AOL as their ISPs) and her Word docs and Adobe PDF attachments are being received as winmail.dat files. We are using Exchange Server 2003 Standard and Outlook 2003. We have read MS articles and Symantec articles and have changed her to plain text only--nothing works. Please let us know if you have a solution to this issue. Many thanks, Stephen Where did you change it to plaintext only? Go into outlook and do it there, where you can do no bold underlining NOTHING, if you do it from the...

Ignoring Blank/Empty Cells that contain formulas
I have a column row of formula's that take data from another worksheet, when there is an error in the original data it leaves the cell blank. Formula =IF(ISERR('JAN 2005'!Y55),"",'JAN 2005'!Y55) My problem is that there is a graph generated from this column and it treats all the blank cells as zero, this messes up the graph and shows it dropping to zero. (The errors alway happen at the end of the data) Is there a way to get the graph to ignore the zero figures or rebuild the formula to show the cell as truly blank? Regards Paul Change the formula to ...

automaticly copy data from one worksheet to another
I am trying to find out if there is a formula that will automaticly transfer a row of data to another worksheet in the active workbook once a certain cell is populated???? just in case this sounds confusing let me clearify.... i have a main spreadsheet that has data for 3 companies, I want to just put a "x" in a certain cell that will then make that entire row of data copy to the worksheet designated for that specific company.... PLEASE HELP!!!!! thanks in advance! Hi Emily, A formula cannot change the value of another it can *ONLY* return a value. For what you want, you would ...

HELP!!! Migrating to Exchange 2000 Exchange2k Setup always Fails! (REVISED)
I posted this before but here is a clearer explanation of what's going on. I have gone throught the procedure below 5 times now this is not a fluke. Please Help! I thought this would make things easier. This my test lab setup procedure from scratch. WHAT AM I DOING WRONG?! 1) Clean install NT4 PDC SP6a (with underscore in domain name to simulate production enviorment) 2) Clean install of NT4 as Member Server SP6a 3) Clean install of Exchange 5.5 and SP4 using domains built in admin account to simulate production enviorment. 4) Build a few mailboxes and user accounts, I don...

CountIf with multiple criteria
I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. =COUNTIF(A1:A100,"<10") =COUNTIF(A1:A100,"<20")-COUNTIF(A1:A100,"<9") -- Gary's Student gsnu200702 &qu...

How to print multiple worksheets in one workbook on the same page.
I want to create a worksheet that have a different number of columns in different sections. If I create a new worksheet for each section, can I print multiple worksheets on the same page within the same workbook? Can I use different column formats within the same worksheet? For example the top part of my form is only 4 columns the sections to follow are 5 to 7 columns wide. rrdiorio, >I want to create a worksheet that have a different number of columns in > different sections. Not if they're above/below one another. > If I create a new worksheet for each section, can I ...

Date Calc. HELP
Hi, My formula: IF($H$37<>MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. W...

Pdf'ing - hyperlinks will be lost
I'm trying to create a pdf with an excel file, but it tells me that links etc will be lost if I don't clear the print titles. The print titles is the repeating first row. This spreadsheet is simply a contact list and is more than one page, so I want to keep ththe first row as repeating on all pages. How do I work around this. It's a contact list, so I want the links to email addresses to remain as hyperlinks. Thanks. Diana Hi Diana, You could easily record a macro to turn them off File, Setup, sheet, Repeat rows at top Print as PDF to a file from the File, Print men...

Barcode Formulas
I am trying to make a barcode that will enter a static time into a cell and move to the next cell (right) I have 3 of 9 barcode fonts. There is no = sign nor is there a symbol for it. Does anyone know how to do this. Other choice would be a button on a toolbar but would like to not have the person return to the computer each time to enter. Any help please. pgriff -- pgriff ------------------------------------------------------------------------ pgriff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26189 View this thread: http://www.excelforum.com/showth...

Help Help!!! RPC over HTTP
My computer has all the certificate authority trusted in my root certification authorities. I still cant get it to work it will only work when I have the computer in the same network. I have Win20003 server, Exchange SP1 and Windows XP SP1. can someone help me this is a stand alone server. I checked everything and everything looks ok. I must be missing something, Note that when I do a https://server/remote it takes me direct to the page because the certificate is already installed. thanks for your help Hi there, i hope you don't mind im joining in your conversation. actually im trying to...

What formula to use?
I have a list of teams in column B. In a tab called Division I have 6 division names with the teams listed in their respective division. I want column C to enter the appropriate division name for the division the team is in. For example, I want column C to be labelled as AL EAST when column B has BOS in it. How do I do this? Thank you. =INDEX(Division!A:A,MATCH(B1,Division!B:B,0)) etc. -- HTH RP (remove nothere from the email address if mailing direct) "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:3100394C-BF57-470D-A218-BCA9C85D8D54@mi...

CONVERSION FORMULA
I WANT TO KNOW IF IS POSIBLE TO DISPLAY ON A CEL THE RESULT OF A CONVERSION FORMULA AN SWITH TO THE ORIGINAL . Hi not really sure what you're asking for?. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany OMAR PEREZ wrote: > I WANT TO KNOW IF IS POSIBLE TO DISPLAY ON A CEL THE RESULT OF A > CONVERSION FORMULA AN SWITH TO THE ORIGINAL . Hi Example: I have a column of electrical corent in volt and i want to know if is posible to put the formula and by a drop down or any other way i can display bouth reding the measure and the converted o...

Multiple Grouping Headers on Form like Reports
Is there anyway to specify addtional grouping headers and footers on forms similar to the way you can on a report? Thanks in advance Not that I'm aware of. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "WAstarita" <WilliamAstarita@gmail.com> wrote in message news:1194472794.211263.160620@22g2000hsm.googlegroups.com... > Is there anyway to specify addtional grouping headers and footers on > forms similar to the way you can on a report? > > Thanks in advance > Would subforms do it for you? -- KARL DEWEY Bu...