I need to create the formula in "Sum state" sheet B2 to sum items for tab AZ (b2:b6). The formula needs to work when I drag it down to other cells.
For example if I drag formula to B3 it needs to adjust the tab from AZ to CA, and add things for that tab. Each cell from A2 :A 11 represent a state and each tab has actual info for each state
I have tabs within my workbook that are pulling from other tabs in the same workbook. There are 30-40 tabs, and only 10-15 are used at a time. Is there a way to hide tabs that are not being used? If I do a calculation in A1 and it's greater than 0, I'd like the tab that is pulling from A1 to be visible. If A1 is 0, then I'd like to hide it.
View 14 Replies View RelatedI have a large amount of information. I need to use three criteria to look up one value however the data now spans across two tabs as there arent enough coulmns to support the data in one tab. I have been trying to use the following:
View 6 Replies View RelatedI'm trying to sum a criteria of all M's in one column that are x's in a different column, throughout multiple worksheets.
I'm able to get the summary number for 1 worksheet using the below formula (*W1 is the worksheet name); however, how do i encapsulate all the worksheets (lets say W1 through W10), please note that some of the worksheets have different ranges (meaning, not all are from Row 2 to 6)
=SUMPRODUCT(--(INDIRECT("'W1'!C2:C6")="M"),--(INDIRECT("'W2'!D2:D6")="x"))
I tried to replace W1 with W1:W10.
I am trying to review a cell range for a specific criteria, and then sum up another cell range if the criteria matches. Here are the formulas I have typed in - there are two columns I am trying to calculate using the same formula, they are next to each other:
=SUMIF('MASTER POINT SCHEDULE'!I2:I841,"0ACA101",'MASTER POINT SCHEDULE'!O2:O841)
=SUMIF('MASTER POINT SCHEDULE'!J2:J841,"0ACA101",'MASTER POINT SCHEDULE'!P2:P841)
I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.
File is attached.
I have a "master" tab that has a dropdown menu (via a validation list) which will determine what the spreadsheet does. Based on the value selected via this dropdown, I would like to show the relevant tabs and hide the irrelevant tabs. I would prefer for this to happen automatically without a macro.
View 5 Replies View RelatedI've been trying to get a macro together which would colour all the tabs in my workbook based on whether or not a certain value exists in a certain cell. In this case the cell is C7 and the word there is "elective class:".
View 7 Replies View RelatedI was recently posed with the following by our PMO: "Can I, or how do I, print only those tabs that belong to specific PM? I know I can go and select just those tabs collectively and print but I hate sifting through these 40+ tabs to find the ones that belong to PM "X". Can't you just use, or create, a macro or something?"
I replied that this might be limitation of Excel but that I would research it and see.
The project template cell reference is D5, there are 11 tabs containing project info for this PM (out of 43), it is running on XP, and is in Excel 2002.
i'm trying to basically use VBA, so that when a list of work tab names are entered into a column in say Sheet 1..these tabs are then selected.
View 4 Replies View RelatedI am looking for a macro to rename tabs based on information in A1 on each tab. The thing that is causing me issues is the cell A1 has a formula in it and it changes on a daily basis with days of the week but the tab only updates when i double click A1 and press enter.
I need it to update automatically.
I have a worksheet with like lots of color coded tabs and the tabs increase everyday. The tabs are categorized based on 6 colors. I created a first tab in order to give how many tabs i have another each color. Is that doable in excel. Do I need a macro for this.
All I want is each time i open the excel sheet the first tab will be automatically updated with how many tabs I have under each color instead of me counting the tabs.
I have a workbook with multiple tabs, each tab is representative of a person and their ongoing project list. What I am trying to do is to be able to pull rows from each tab identifying the persons active projects based on an assigned due date.
Details: Tabs for Tom/Susan/Phil/Jerry each person will be updating their own sheet with project completion details. Each tab has similar headers with a corresponding due date or completion date. On the master sheet I would like to pull those rows that correspond to the specific dates for all persons.
Example - give me all rows that reflect projects due on 1/21/2014.
I work with contracts and have to report the sales for each contract by customer number, name, address, product code or sku, sales amount, quantity, and invoice date. Each contract number is associated with a product group and within that product group are a laundry list of product codes or skus.
Customers are often on different contracts for different products and may purchase some or all of the products on that contract. When I run a query from the system to extract this data, I get one sheet that contains 20,000 lines that have to then be sorted and seperated into different tabs based on the contract number. Keep in mind that a customer may be listed multiple times because each line in the query represents a product code and an invoice date.
I currently have a macro that will format the reporting such that it sorts the data, however, what it doesnt do is look for each instance of a particular contract number and separate it into different tabs.
So below are two examples: April and May 2008 Reports
April 2008 Report
Contract 1 contains 5,000 lines of data - extract into new tab
Contract 2 contains 5,000 lines of data - extract into new tab
Contract 3 contains 5,000 lines of data - extract into new tab
Contract 4 contains 5,000 lines of data - extract into new tab
Total lines = 20,000
May 2008 Report
Contract 1 contains 7,500 lines of data - extract into new tab
Contract 2 contains 5,500 lines of data - extract into new tab
Contract 3 contains 8,000 lines of data - extract into new tab
Contract 4 contains 9,000 lines of data - extract into new tab
Total lines = 30,000
So for the April Example, I would like excel to take this one list and break into 4 new tabs based on the contract number and carry over all of the data into that tab.
Where this may become difficult is that there may be more lines of data one month and fewer another. Im not sure if there is a function that will tell excel to stop when it reaches the last line for one contract number and to start a new tab for the next contract number. (see the month of May example)
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets.
Eg.
In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically.
In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
How would I reorder columns & tabs based on a form where the user can put them into order that they would like the columns? I forsee some type of form where the user can dsignate the order and then start a macro that will sort both the columns and tabs.
View 5 Replies View RelatedI have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedI have 5 cities and each city has a set of data in separate tabs. Right now i have data pulled for all 5 cities in a single sheet but its too cramped up..
I want to use a drop down list (data validation) and if any of the 5 cities are selected in the drop down, the data from that particular city (tab) should be pulled up. This way my sheet size will be reduced 5 time which will look good.
A common v lookup wont work because the data has to be pulled from 5 different tabs for 5 different cities.
I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc).
What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).
I want to create a macro that creates a new .xlsx document based on cell values. And input 4 tabs into each document with specified tab names. I have a document that pulls from external sources and fluctuates with amount of data per day. I envision something that will make a new document named for the contents in cell A1, then function as a control+down to create a new document for A2, then A3 until there is no content left (should mention these are lookup formulas, and if there is no data it pulls a value of "0")
Is there a way to put in the code, the tab names it would create in each new document, or would that have to look to a cell value for the naming? The tab names would be "GS", "MYSS", "COLL SHEET", and "WIRE".
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedExcel 2007
My workbook contains 13 tabs - 1,2,3,...12, and Summary
My data starts on line 4 of every sheet but varies in length - so far the longest goes to line 30.
Rows used on all 13 sheet are as follows:
A - contains facility names
B - contains a two or three letter code
C - contains hours
D - contains dollars
E - contains adjusted rate
On the Summary tab I have listed all the facilites and two or three letter codes. I need to sum column "C" on tabs 1-12 when they match columns A & B on the summary tab. I have tried the following but can't get them to work:
=IF($A5=""," ",SUMPRODUCT(--('1:[12]12'!A$4:$A$50=$A5),--('1:[12]12'!B$4:$B$50=$B5),'1:12'!D$4:$D$50))
I did not put the [12] excel added that automatically I had 1:12
=SUMPRODUCT(--(THREED('1:12'!$A$4:$A$50)=A10)*(THREED('1:12'!$B$4:$B$50)=B10),(THREED('1:12'!C4:C50)))
I just seen the THREED for the first time today and am not sure if this was the correct place to try but it didn't work anyway
My main data tab is collecting gallons pumped for a particular piece of equipment. I have a drop down box to populate the piece of equipment and VLookup to identify the unit number associated with the equipment. I would like to create a separate tab for each piece of equipment that will track the number of gallons pumped during the calendar year. Here is the format for the main tab:
DateBeginning Meter ReadEnding Meter Read GallonsEquipment IDEquipment Description
1/2/2014565443565625 18212006 ford f250
1/10/2014565625565675 5022006 Chevy 2500
1/11/2014565675565750 754Ford Taurus
1/12/2014565750565830 8012006 ford f250
1/13/2014565830565900 7012006 ford f250
1/14/2014565900566000 10012006 ford f250
1/15/2014566000566125 12512006 ford f250
1/16/2014566125566215 9012006 ford f250
Here is the format for each piece of equipment:
2006 Ford F250
Date Gallons
1/2/14 182
1/12/14 80
1/12/14 80
1/12/14 80
1/13/14 70
1/14/14 100
1/15/14 125
1/16/14 90
The formula I used in Cell A4 is =INDEX(Gasoline!A4:A23,MATCH(1,Gasoline!F4:F19,FALSE),1)
Cells A4 and B4 have the correct values. My problem comes in A5,A6 and B5,B6. These should not have a result because they are different pieces of equipment. Throughout the year, each piece of equipment is going be used on the main tab. How do I keep the individual tabs from picking up the same entry multiple times.
I'm using Excel '03SP2 on Win 2000sp4. I believe I have a case where old tabs originally written in Excel ' 97 behave differently than new tabs in the same workbook, which is saved in 2003 format.
Specifically, the formula ="PLAN" & budyr-1 where budyr is a named range on a different tab containing 2008 . This formula yields PLAN 2007 on a new tab and #VALUE! on an old tab.
How do I get Excel to update the old tabs so that they behave as Excel 2003 tabs?
The Table below outlines a scenario i have..
I am looking for a sum that looks at Colum A: to determine if it is an old version and new. So G2 should If it is marked with the word "New" give me the sum to column F2: otherwise give me the the sum of B2:E2.
Was looking at Sumif but not can't seem to get the formatting right.
A
B
C
D
E
F
G
[Code] .....
I've got a sheet called DATA with a series of columns. Column Q has a series of numbers throughout the rows. I need to input a formula in a cell that says, everytime column Q is = 2, calculate the sum of those rows in column N.
The second one is a bit more challenging. A few cells in column F contain the number 1 in them. I need a formula that calculates the average of the cells in column C wherever there is a 1 in column F.
I am trying to create a sliding fee scale for a medical practice. Essentially it will categorize patients by family size and income level. The table which the scale is based off of is as follows: The far left column is family size (1-10) followed by 4 monthly income levels (ie. 1000, 1200, 1400, 1600). The table is based in the federal poverty line (FPL). I need to create a lookup formula which will reference this value and generate the appropriate category based on the income and family size of the patient. For example, according to the table a 3 person family which earns less than 1000 is in category A, but between 1000 and 1200 is in category B.
View 9 Replies View RelatedI have 1 worksheet which consist of few products for 6 months which I want to
sum up the individual product cost for certain period ie. YTD for 3 month, YTD april, and Q2 (april to June). The result is to appear in another worksheet. Try to use sumproduct, but unable to get what i want..