With-in one workbook I am trying to link several spreadsheets� entries into one colum

I have one spreadsheet titled "Collections" and another titled "Customer
Service."  The last spreadsheet is titled "Resources."  Each
spreadsheet has every day of the year listed in column A1 down.  My
goal is that if any entry (like the name Chuck) is made into any
cell(s) - other than the dated cells - on Training 1 &/or 2 that entry
is populated into a specific column on the Resource spreadsheet. 

In other words:

I want the entry �Chuck� in E10 on the Collection spreadsheet to
populate in B10 on the Resource spreadsheet.
I want the entry �Chuck� in C22 on the Customer Service spreadsheet to
populate in B22 on the Resource spreadsheet.
ETC.
ETC. 

If the entry �Chuck� is populated more than once in any dated row on
any spreadsheet I need an error to populate in the matching cell in the
designated column on the Resource spreadsheet.

I think that I�m almost there with this formula:
=IF('CUSTOMER SERVICE'!A:H="CHUCK","CHUCK", "")
However, this argument doesn�t put the entry in the specific designated
column on the Resource spreadsheet.

Make sense?


---
Message posted from http://www.ExcelForum.com/

0
1/29/2004 9:59:23 PM
excel 39879 articles. 2 followers. Follow

2 Replies
369 Views

Similar Articles

[PageSpeed] 11

Chuckg <<Chuckg.10tcsx@excelforum-nospam.com>> wrote in 
news:Chuckg.10tcsx@excelforum-nospam.com:

> I have one spreadsheet titled "Collections" and another titled
> "Customer
> Service."  The last spreadsheet is titled "Resources."  Each
> spreadsheet has every day of the year listed in column A1 down.  My
> goal is that if any entry (like the name Chuck) is made into any
> cell(s) - other than the dated cells - on Training 1 &/or 2 that entry
> is populated into a specific column on the Resource spreadsheet. 

I'll assume that all entries in Collections and Customer Service are in 
columns B through E.  If that's not the case, it's probably better to use 
Visual Basic.

> I want the entry �Chuck� in E10 on the Collection spreadsheet to
> populate in B10 on the Resource spreadsheet.
> I want the entry �Chuck� in C22 on the Customer Service spreadsheet to
> populate in B22 on the Resource spreadsheet.
> 
> If the entry �Chuck� is populated more than once in any dated row on
> any spreadsheet I need an error to populate in the matching cell in the
> designated column on the Resource spreadsheet.

put in cell B1 of resource spreadsheet
=if((not(length(Collections!B1))+not(length(Collections!C1))+not(length
(Collections!D1)+not(length(Collections!E1))+not(length([Customer 
Service]!B1))+not(length([Customer Service]!C1))+not(length([Customer 
Service]!D1)+not(length([Customer Service]!E1)))>1,"Error",(Collections!
B1)&(Collections!C1)&(Collections!D1)&(Collections!E1)&[Customer 
Service]!B1)&[Customer Service]!C1)&([Customer Service]!D1)&([Customer 
Service]!E1))

and fill down




-- 
My email address has an extra @ (spell it out) and an extra invalid. 
Please remove them if you are not a spammer or list broker and want to 
reply.
0
pqaf4hm02 (126)
1/30/2004 4:02:42 PM
Jonathan Rynd <pqaf4hm02@atsneakemail.com.invalid> wrote in 
news:Xns9480707484CFjrr7hotmailcom@130.81.64.196:

> put in cell B1 of resource spreadsheet
> =if((not(length(Collections!B1))+not(length(Collections!C1))+not(length
> (Collections!D1)+not(length(Collections!E1))+not(length([Customer 
> Service]!B1))+not(length([Customer Service]!C1))+not(length([Customer 
> Service]!D1)+not(length([Customer Service]!E1)))>1,"Error",
(Collections!
> B1)&(Collections!C1)&(Collections!D1)&(Collections!E1)&[Customer 
> Service]!B1)&[Customer Service]!C1)&([Customer Service]!D1)&([Customer 
> Service]!E1))

Oops, my bad -- I should have used apostrophes instead of square 
brackets.  Try

put in cell B1 of resource worksheet
=if((not(length(Collections!B1))+not(length(Collections!C1))+not(length
(Collections!D1)+not(length(Collections!E1))+not(length('Customer 
Service'!B1))+not(length('Customer Service'!C1))+not(length('Customer 
Service'!D1)+not(length('Customer Service'!E1)))>1,"Error",(Collections!
B1)&(Collections!C1)&(Collections!D1)&(Collections!E1)&'Customer 
Service'!B1)&'Customer Service'!C1)&('Customer Service'!D1)&('Customer 
Service'!E1))

-- 
My email address has an extra @ (spell it out) and an extra invalid. 
Please remove them if you are not a spammer or list broker and want to 
reply.
0
pqaf4hm02 (126)
1/30/2004 4:09:55 PM
Reply:

Similar Artilces:

Open workbook to specific worksheet
Is there a way to have a workbook open to a certain worksheet regardless of which worksheet was active when the file was saved and clsoed? Example: Worksheets in workbook - data, budget, analysis, comments Saved and closed with data worksheet active On open have budget worksheet active Thanks Dave, you could put some code in the workbook open event like this Private Sub Workbook_Open() Sheets("budget").Select End Sub To put in this macro, from your workbook right-click the workbook's icon and pick View Code. This icon is to the left of the "File" menu this...

Strip Flat File Table into less entries
I have a table I found that is essentially a 'hierarchical' table but laid out in such a way I can't really use it w/o further processing. Essentially it is currently something like this Type | Type Code | Subtype | SubType Code | SubSubType | SubSubType Code I really just want to end up with Type | Type Code | Parent Code So I might have Fruit | 100000 | Citrus | 100100 | Orange | 1001001 Fruit | 100000 | Citrus | 100100 | Lemon | 1001002 Fruit | 100000 | Melon | 100200 | Cantelope | 100201 Fruit | 100000 | Melon | 100200 | Honeydew | 100202 Meat ...

can excel check one column against another
Hi. I have a spreadsheet with columns such as A, B, C, D, filled with numbers. Each row across such as row 1, then row 2, then row 3, etc of numbers is totaled in column E. Countless rows, each of which is totaled per row and then ends with a grand total at bottom of column E. Next comes columns F, G, H, also filled with numbers (but different numbers), which is totaled per row in Column I. At the bottom of Column I is a grand total. Even though arrived at using different sets of numbers, -both grand totals - in columns E and I must match. Sometimes they don't match. Ouch. So here is m...

why crash in the second method of one interface
Hi, Using one class generated from *.tlb to call 2 methods of one COM. The strange thing is that I got one error of the "First-chance exception in : 0xC0000005: Access Violation" in the second method. I also use ASP web page to call both methods of this COM and both methods seems is OK. ITemplateManage mytemplate; if (!mytemplate.CreateDispatch("comobj.TemplateManage")) { IDE_Error::Report (ComError|1,"ComObj.TemplateManage in FUN_Template::Add()"); return false; } result = mytemplate.Check (templatefilename); decodeparameter=mytemplate.GetInfo (templ...

Links in Outlook #4
Links that I used in the past from my e-mails now do not go to web page. Instead I get a box that asks about excutable files. (Locate Link Browser) How do I change settings to get back to being able to use links in e-mails that are to trusted sites. Control Panel->Internet Options->Programs Tab->Reset Web Settings. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Jim asked: | Links that I used in the past fro...

Entry Point was not Found
The other night I was copying a large file to an external USB hard drive. I decided to go to bed and when I woke up and returned to the PC, I saw a BSOD. And damned if I now can't find the scrap of paper I wrote down the details on!!! Sorry [hangs head]! I will post back when I locate it. It was a memory dump. Anyway, I Googled and the only suggestion that I thought was worth trying was to open a command prompt, change the directory to C:\windows\system32, and enter the following command: regsvr32 usbmon.dll I wasn't sure if this would fix the problem, but I figure...

MSR Credit Card Reader adds all the information on one line
Is there a way to get the CC reader to properly seperate the the acct # from the expiration date? When I swipe I currently get everything on the acct # line. Thanks in advance! What type of CC reader(MSR) to you have? Typically this is an issue with simply configuring the MSR. Rob "Steakum" wrote: > Is there a way to get the CC reader to properly seperate the the acct # from > the expiration date? When I swipe I currently get everything on the acct # > line. > > Thanks in advance! Its a IDTECH Minimag USB/Keyboard Interface Do I need to select the Wedge Key...

Duplicate Transactions, One When Pending, One When Posted
I'm sure this has been addressed, but I couldn't seem to find an answer searching: My problem is, a transaction occurs at my bank, and MM2K7 downloads the data for it. Once that transaction moves from "Pending" to "Posted" at my bank, the transaction data changes, so MM downloads it again. Sometimes I get a possible duplicate message from MM, sometimes I don't. What are people doing to handle this situation? Thank you. This is a problem with your bank, not with Money. Eventually they should fix it. Call them. In the meantime VOID the pending transacti...

excel links #5
Running excel X for a mac, 2001. Have workbooks with links that are not locatable by searching for "!" in worksheets, and link to deleted Workbooks. Am unable to find link source, or find link destination workbook, or change links. Copying worksheets to a new Workbook takes the links with them. Have exhausted net search. Can anyone help me to kill these pesky links, please? Mike Sometimes the links are in Names in Insert>Name>Define. In addition.............. Download Bill Manville's FINDLINK.XLA from Stephen Bullen's site. http://www.bmsltd.co.uk/MVP/Default....

Customized desktop Icons for Excel workbooks
I have ceated a rather powerfull workbook using excel that helps my company manage a large range daily tasks. This workbook is used by all office, sales and management personel. A copy of this is kept on thier desktops. I have been trying to come up with a way to change the appearance of the workbooks Desktop Icon (maybe company logo) in hopes of making it stand out among all the other Excel Icons on the desktop. Is thier anyone out there who can help? -- Trimmer Put the excel file itself somewhere other than the desktop (My Documents?) and put a shortcut to the file on the desktop....

grid off on one page
Is it possible to turn the grid off of only one page? thanks On Thu, 14 Feb 2008 10:37:11 -0600, "greg" <iuouh@ghfhg.com> wrote: >Is it possible to turn the grid off of only one page? Not really as it is a document wide setting.... however, there is workaround open the page shapesheet menu window -> Show shapesheet and set XGridSpacing and YGridSpacing to a large value. -- Regards, Paul Herber, Sandrila Ltd. SSADM for Visio http://www.saadm.sandrila.co.uk/ On Thu, 14 Feb 2008 16:47:53 +0000, Paul Herber <SubstituteMyFirstNameHere@pherber.com> wro...

Execution time of several minutes
Hello group! A couple of weeks ago, Bob Phillips helped me with a function that rearranges and sorts my list of run times (for different courses when I've been out running). Now, I'm just wondering, is such a function supposed to take 3-4 minutes to execute (on a slow, 800MHz/384RAM (not the newer, faster type))? For each course that I've run (currently about 15 in the "course list"), the function runs through my list of runs (which consists of results from about 90 runs) so that the script can make a new listing of all runs, but this time grouped by course. Thereafter,...

One to One relationship
I have an Events entity that is an offshoot from the Contacts entity. Basically, it tracks what events each contact has been invited to, and is used mainly by the Events Coordinator. I've set it up as a One (Contact) to Many (Events) relationship. However, I really only want one event form for each contact form. The reason I've set up a different entity rather than just adding a tab is that access to this entity needs to be tightly controlled. Obviously, if many events forms are created for the one contact, it would be very difficult to tell which one is accurate etc. Is it possibl...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

route inbound email from ip to one folder
Is there a way to route any incoming email from a certain IP address to a single folder? Our company is receiving emails from a source that we do not wish to receive, and would like these email to be directed to a singe folder rather than blocking them. Any ideas? Thanks. you could send them to the turf dir... http://support.microsoft.com/default.aspx?scid=kb;en-us;245465 "daine" <anonymous@discussions.microsoft.com> wrote in message news:11d501c4263d$f4d05110$a101280a@phx.gbl... > Is there a way to route any incoming email from a certain > IP address to a single ...

Removing automatic links
Hi all I cut columns from one work book and pasted it in another. Now when open it, a prompt states that it has automatic links and to eithe click yes or no. Is there a way of getting rid of this because I want to deleted the ol workbook or am I stuck with it? Many thanks guys Joe -- Message posted from http://www.ExcelForum.com The easiest way to find the links is to perform a search or a replace If the cells contain formulas that refer to the original workbook an need to refer to the current workbook you could try replacing th workbook name with a blank replacement. This will only wo...

One client (Server 2k8-R2/HyperV) will not sync with DPM
We have been running DPM successfully for about a year now without any major problems. Recently we added a new server - a fairly robust PowerEdge that will be housing 4 HyperV VMs. For whatever reason, I can't get anything but the HyperV "Initial Store" to sync correctly. I thought initially it might be a problem with the HyperV machines, but it also fails to back up a small directory of files on the host drive. The oddest part about this is I really don't get an error. It will try an initial sync and run for a day or two and show very minimal data tr...

To view, specifically Units in one report
I have report with a Reporting Tree (9 Unit), when the report is generated, I select only two unit, but I need that the drill down show one report with these unit only If you want multiple units in a single report you will need to use the column layout with unit restrictions on multiple columns Leslie "Pedro" wrote: > I have report with a Reporting Tree (9 Unit), when the report is generated, I > select only two unit, but I need that the drill down show one report with > these unit only Leslie; Each Column layout have the unit restrictions Thanks :-) "Lesl...

CSV error when trying to save
I'm using Windows Vista X86 and Word 2007 and I'm trying to save a workbook in the CSV file format. Everytime I save, it gives an error saying the file name.csv may contain features that are not compatible with CSV (Comma delimited). I was wondering if I would lose data or if data would be corrupted because of this? Many Thanks Brandon G. It is not an error, it is a warning, just letting you know that the csv will contain plain text, no formatting, no formulas, etc. "Brandon G" wrote: > I'm using Windows Vista X86 and Word 2007 and I'm tryin...

SOP Transaction Entry
We have just started to use SOP for our invoicing to customer. We are just getting it started for October 1. However, we have a couple quirky things that we need to show on the customer invoice. We are a printer and sometimes do donations of print work. However, we recognize it as a print sale in total and then do a credit for the donation portion to expense to our contributions account. I am trying to find out how to do this as an SOP invoice. We usually show the customer the amount of donation and the balance due on their part. I tried to enter a negative amount in the misc line...

Exchange 5.5 - One Organization
Hello - I am having a problem that I would like some more information on. I have One Organization (5.5) and 16 Sites (5.5) and would like to know why I can't stop the other sites from sending mail through me. If I can, I would like to know how. Thanks for any help. what do you mean by "sending mail through me"? What mail? Internet email? Does each site have its own Internet Mail Service? Or is your site kind of the "hub" where all Internet email comes in and goes out? -- Susan Conkey [MVP] "dave.mudgett" <davemudgett@discussions.microsoft....

See more than one tab at a time
I would like to be able to see more than one Tab at a time in Excel. Currently I'm able to see two Tabs from different worksheets, but not two or more tabs from the same worksheet On Sat, 28 Aug 2004 14:57:03 -0700, "rocky59" <rocky59@discussions.microsoft.com> wrote: >I would like to be able to see more than one Tab at a time in Excel. >Currently I'm able to see two Tabs from different worksheets, but not two or >more tabs from the same worksheet That would be because each tab represents a single worksheet. Clicking on the tab would move you from where ...

Updating formula/links
Hi, A problem with formula: I have a sheet that gathers information from a number of xls files. Now when the year has changed some of those files are not necessary and I want to get rid of them. If I just delete them, at the time of updating links, the gathering sheet complains because it can't find those files. I sure wouldn't like to do it cell by cell because there are million cells to update... So is there a way to tell to the sheet that those files are gone for good and to make it stop looking for them??? Thanks in advance!!! --- Message posted from http://www.ExcelForum.com/...

Why Would You Lose Sound On One User
WXP Pro Version 2002 Service Pack 3 with all current updates Dell Pentium(R) D CPU 2.80Ghz 2.79Ghz, 512 MB RAM System has three users all with admin rights - User 1(primary - me), User 2 (wife), and System Admin. (which I made visible on desktop). I (User 1) have lost sound when attempting to play CD's, and other audio files using WMP 11, however, I can still hear system sounds. User 2 and System Admin. still have full sound. System restore does not help ( in this case I only had a restore point from 5 days ago. Any thoughts? -- Skeeter "Old age and ...