Help Please - Match & Index Functions (I hope)!

Please post response as email is bogus to prevent spam.

I am using Excel 2003. I hope I am clear in my description of my need.

I get a weekly sales report and want to roll the numbers up into a
ROLL UP sheet. The roll up will be static but the weekly sheet is
dynamic (can fluctuate in the reporting). Universal to both is a store
number, a UPC (identifier), and amount sold.

The weekly sales will appear across columns (for tracking purposes).
Example: 06-09-05 data will appear in column D, 06-15-05 data will
appear in column E, etc.

The other info will be like shown down column A:

Item 1
Item 2
Item 3
Item 1
Item 2
Item 3

In the roll up I want to put a function that will search the weekly
data (I know I will have to change source page name in the function
weekly) that will give me any sales for store 101 of the three items.
Each week I will build a new function for the column I want the data
to appear in. 

Now, items may have sales, so the store number would show up with item
and amount, or if there are no sales store number may not appear on
weekly report.

I have tried to think this out but not sure how to proceed. Is this a
Match function, an Index function, or a combo of both?

I appreciate any help or solution.

Thanks in advance,


donotuse (3)
9/2/2005 1:42:42 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 48


Similar Artilces:

Help Needed on Complicated Query
Hi- I have a database that has a number of projects and suppliers for these projects. Each project has several contracts being supplied by several suppliers. I have a suppliers form with a contracts subform. What I want to do is create a subform inside the contracts subform where i will be able to add and edit invoices that have been billed against that contract. The matching data is: Same project, supplier, contract Here is the relationship diagram:[]=1623882 I need help forming the SQL statement. I'm so...

I need help changing the budget period!
I cannot find where to change the budget time period in this program--I want my budget month to start at the 10th and end at the 9th of the following month instead of the traditional start at the 1st and end on the last day of the month. Does anyone know how to do this! ...

formula help #47
This is a condensed version of an inventory sheet. It is an inventory of 5 different machines. The sheet is one continuous sheet. Management just wants the total count of each candy charted out by date, by candy. So there will be a line for Snickers plotting the count for 10/2, 10/9, 10/16, 10/23. Then another line for Baby Ruth plotting the count for 10/2, 10/9, 10/16, 10/23. Ect… I am assuming I will have to insert a Bin worksheet to capture the data and sort it. I am lost as to where to start. Any help would be GREATLY appreciated! (The chart is not really the issue. I think I ca...

Help with a template file
Im really new here so apologize if this has been asked before. My problem is this: I have created a template file that will be stored on a share drive company wide. What I need is something that when someone uses that template, it will create a unique number in a certain cell and then not change everytime the new .xls file is opened. I would like to have that number pull from 3 cells if possible. I do understand a little bit about macros from what I have learned from this discussion group. But please if its really complicated help me with step by step. Thank You A unique number? Like ...

Need some help with a formula...thanks in advance
I have a tab called Record. Here are the cells it contains: A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't ...

SQL query Help 09-01-10
CREATE TABLE [dbo].[FileHeader]( [LedgerKey] [uniqueidentifier] NOT NULL, [SubmittedDate] [smalldatetime]NOT NULL CONSTRAINT [FileHeader] PRIMARY KEY NONCLUSTERED ([LedgerKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[FileBody]( [LedgerKey] [uniqueidentifier] NOT NULL, [TransactionID] [uniqueidentifier] NOT NULL ) ON [PRIMARY] INSERT INTO [FileBody] ([LedgerKey] ,[TransactionID]) VALUES ('F92B4920-D748-4DF8-9182-E6C524F7840A&#...

I am trying to write a simple dialog form and use the appwizard that comes with MS V C++ 6.0. Under "Resource View" tab I have layed out my form as I would like it to look. I want to be able on the click of a radio button to enable or disable the Edit region I have created. I do not understand how to expose that object so that other classes can have access to it. So i have two classes called CDSXGUIApp AND CDSXGUIDlg. When I go to create a function for the action of lick on the radio button it puts the function in CDSXGUIApp. If I define a Edit Region called IDC_EDIT_FNAME ...

OOO Help
I am an Exchange Administrator. LOL. Is this the correct lien of code in OOO to send OOO to outside SMTP's? >-----Original Message----- >Benjamin Kirby <> wrote: > >> I want to setup an OOO and I thought there was a line of >> code or a switch you put at the bottom of your OOO >> message to send to outside SMTP's along with all the >> users in the network GAL. > >The Exchange administrators can enable this function, if they choose. >-- >Brian Tillman >. > .. See;

Auto PO re-Order Help
Ok I use auto re-order to generate PO from lets say X Corp. Min. set @ 10 and Max set @ 40, so I generate Po which auto re-orders 30 Items from X Corp. Now 2 days later I need use auto re-order to create another PO from same vendor but not fpr the Items already on 1st Po/In transist but it does add these same Items if I have sold 3 it tries to order 3 even know I have seven in stock and 30 on the way? Is there a setting to make this program see that I already have 30 on order? -- Proink Have you pout the PO as "Order Placed"? as I know system counts qty on PO. "Proin...

ISA Server 2000 and RPC over HTTP...Tom S. -- HELP!!! (please)
I'm attempting to get RPC over HTTP through ISA Server 2000 to work. My setup is: 1) ISA Server running on W2K Server 2) Exchange Server 2003 running on Windows Server 2003, SP1 I've looked through Tom's 3-part series, but his articles relate to a FE - BE setup -- in my case, FE/BE are on the same box. I've also perused much of the documentation put out by MSFT, but I'm still not making any progress. Is there definitive documentation out there that describes how to configure RPC over HTTP with ISA Server 2000 firewall? Thanks in advance for any and all responses? ...

Index in Outlook 2007
After having no problem in XP with WDS 3.0 the OL2007 mail folders don't get indexed anymore after upgrading to Vista, although OL is listed in the "indexed"-places in the search-options. Can anybode help? Henrik did you rebuild the index after upgrading? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? Outlook 2007: Outlook Tips by email: Outlook Tips:

Gets PK from index that doesn't contain it.
On Sql Server 2008, I am looking at a test where I am using PUBS and added an index: CREATE NONCLUSTERED INDEX ncl_authors_phone ON authors(au_lname, au_fname, phone) But when I do: SELECT au_id FROM dbo.authors WHERE au_lname = 'White' AND au_fname = 'Johnson' The query plan shows it getting the au_id from this new index (which doesn't have the au_id in it). SELECT [au_id] FROM [dbo].[authors] WHERE [au_lname]=@1 AND [au_fname]=@2 |--Index Seek(OBJECT:([pubs].[dbo].[authors].[ncl_authors_phone]), SEEK:([pubs].[dbo].[authors].[au_lname]=[@1] A...

Need help on setting up users
Hello all, I am a complete newbie when it comes to CRM. I finally got it installed and I can log into it now http://ironwood-crm:5555 I go to my url and get prompted for credentials and I put in my user ID and Password which I am domain admin with all privelages. Now I want to set up a few other users so we can get to testing and setup. I go to Settings/Business Unit Settings/Users I see me as the only account. If I try and add my boss for example I get an error. The setup is SBS2003 fully patched with SQL Server 2005 standard edition I think and CRM 3.0 all installed on the same machine. I am...

Suddenly Dazzle Can't connect to Outlook 2003 - Interface Error
All of a sudden, my postage printing application Dazzle gets an error when it attempts to access my outlook address book. Previously I would get the message box that an application was trying to access the address book and was that ok.. now I get an error that says �Outlook interface error: Unable to communicate with Outlook.Application {Class ID 0006F03A��} error 2147942526 with a bunch of zeroes after the class ID value that I copied. Any thoughts? Very frustrating. TIA. And Dazzle support said??? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discus...

Change password message does not match PSO
Hello all, I have created a PSO in ADSI edit for fine grained password policies in Windows Server 2008. The settings in this password policy include: complexity yes, min password length 8, password history length 12, min password age 1 day, max password age 90 days and a few more that were required when I created the policy. I applied this policy to a group in AD and placed one user in this group. When I logon with the user and try to change my password to something that does not comply with this policy I get the message:"Your password must be at least 7 characters, c...

Lookup Function and Changing Data Sets
I have a single database that is queried using the LOOKUP funtion. Thi function is propogated on about 30 worksheets throughout the workbook. Unfortunately the database is not static and will gain incremental dat overtime. Is there an easy way to change the array in my LOOKUP function (t accomodate the additional data) and have it automatically updated i all the funtions throughout the workbook? Thank -- Message posted from create a defined name for the lookup table such as insert>name>define>name it whatever>in the refers to =offset($a$2,0,0,count...

Excel on Vista functions?
I wanted to know if any on has any News on the Functionality in Vista Excel / Office 12? How is the next version developed? Can I get a link on the features. Have a look at Office 12 'here' ( -- ashwink ------------------------------------------------------------------------ ashwink's Profile: View this thread: ...

Outlook Installatin Big Problem ... Need Urgent Help
Hi I'm trying to install the outlook client (laptop) in a computer but it gives me an warnibg msg : "The specified domain either does not exist or could not be contacted" when I press next, in the middle of the installation it gives me an error :Action Microsoft.crm,setup.client.installabpAction failed Exception from Hresult: or8004010F The computer is in workgroup and not a domain (I'm not sure what it means...) The web client is working perfect Need help ... -- Thx, Omry The laptop must be joined to a domain. If you don't know what that means ask your network a...

Match and Sort for two range of data on different worksheets?
Hi all, I had a tedious problem here. I had two worksheets on e same workbook whereby I have one range of data on Sheet 1 and e other range of data on Sheet 2. [Data given to me]... The data on sheet 1 had info on 1st half of financial year whereas e data on sheet 2 had info on 2nd half of financial year. It appears as below: Sheet 1 (1st half) Sheet2 (2nd half) --------- -------- Company Name Sales Fig. Company Name Sales Fig. BAA Limited ...

Getting my Library to match my Folder structure
This question has been asked before in various incantations, but I have to blow off some steam here. I WANT TO DELETE MY MUSIC LIBRARY THEN HAVE IT RESCAN MY MUSIC FOLDER (D:\MUSIC) PICKING UP JUST WHAT IS THERE AND REFLECTING THE DIRECTORY STRUCTURE WITHIN. The reason is that the "Library" keeps showing me some duplicate entries and a couple of "artist" folders not named the way I want. I assumed I could kill the library database, and have it rescan the folder. Unfortunately I haven't been able to get the old library deleted for good. Now, I have t...

I have 2 checking accounts with my bank. I've been using msn bill pay for the 1st account for quite a while and it works great. I've recently set up the 2nd account and gone thru the verificiation step in msn bill pay. Both accounts show up fine on the msn bill pay site and I've paid a bill via the banks bill pay service from the 2nd account. However, when I try pay a bill from within Money 2007, I only have the option for "write check." Any ideas? I am sorry that I do not have any information to help you other than to say that most all banks that support billpay ...

Help with iif statement in query
I have a query which has the following iif statment: Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24) This tells me how many hours have been accrued for vacation in 2008 I have another iif statment in this query which is: Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued Vacation],[Vacation_Balances]![Vacation]) This statement is returning a blank cell for those employees who it should be returning the result from the previous iif statment, however it is returning a blank cell. Can someone help me with this - please let me know what other information is nec...

Function needed to search and pull data from 1 table to another
Really need some help to determine what function/formula to use. Table A contains names of Learners and their job functions. The same learner could be there twice, and some learners have no job funtion. Learner Name Job Function A, EASWAR Marketing A, EASWAR Marketing Aarts, Hans Abib, Marcelo Research Abib, Marcelo Research Abo El Azayem, Mohamed HR Abou-Taleb, Khaled Marketing Aceña Garcia, Luis Sales The second table just has learner names. I need to pull the job function from the other table to this. However, note that there could be someone on this list that is no...

HELP Please.. OWA problem
Hello all I am having a problem with OWA. I have just installed a new Exchange 2003 server into a 2000 org. Everything was working fine, I was in the process of moving mailboxes. One of my guys noticed that the default website was not pointing correctly in IIS. It was set to redirect to a URL on another server. So this gentleman changed it, and the corporate web site came up fine. However, OWA did not. All of the sites (including Exchange) started redirecting. I can understand this behavior, but how do I set OWA back again? What I an getting now are 503, service unavailable errors. ...

Help needed ASAP
When I make deposits into my business checking account, they show up correctly as income on my profit/loss statements. However, on my MSMoney home page, I have Business Snapshot as one of my views. On the revenue line, it is zero. Why? When I click on the Year to Date Revenue link, it correctly shows my Profit and Loss statement with the correct amount under Total Net Sales and Gross Profit. Why doesn't this translate into the Year to Date Revenue line? ...