I can't attach the file as it is too big for the site. The file has a front sheet called "Viewer". The filters at the top used to work. They would reduce the results in the table underneath. For some reason this is no longer working.
It uses the lookup tables on the Lookup tab but then uses arrays I think.
I have a macro that used to create a new book for each of the worksheets in a workbook that would name them the same as the worksheet.
Since our company upgraded to Windows 7 the macro, more often than not, fails to work.
The code for the macro is
Code:
Sub NewBook1()
'
Set wbSource = ActiveWorkbook
For Each ws In wbSource.Sheets
ws.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & filePrefix & " " & ws.Name & " " & fileSuffix
wbDest.Close
Next
Application.ScreenUpdating = True
'
End Sub
This vba code is supposed to move the cursor to the Cacluate1 command button when the user clicked on cell H6.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$H$6" Then Calculate1.Activate
End Sub
The only changes that I have made is a change to a private sub worksheet_change(ByVal TArget as Range). Would these changes have anything to do with this no longer working?
I have been working on this application for the past couple weeks and I have this module of code that copy's a worksheet into a new workbook and changes everything into values. However I have added buttons onto this sheet and after running the code again it froze the program, in which I had to manually end. It gave an error afterwards however I don't remember what it said. Now everything I try to run or step through it, nothing happens. And I have turned Events and ScreenUpdating on.
I would also like to note that it won't allow me to perform actions like unhide sheets or unprotect them. The code has not been changed either so what has happened to disable all functionality.
[URL] .....
I have been working on this application for the past couple weeks and I have this module of code that copy's a worksheet into a new workbook and changes everything into values. However I have added buttons onto this sheet and after running the code again it froze the program, in which I had to manually end. It gave an error afterwards however I don't remember what it said. Now everything I try to run or step through it, nothing happens. I have turned Events and ScreenUpdating on.
View 2 Replies View RelatedBelow is my attempt to understand working with arrays of values.
Test() works fine. Yay! But it is "too easy" just plugging in a hard value for the range address...I don't always have that luxury. Normally I work with ranges that I've determined at runtime. I'd like to assign a range of values to an array but my attempt below -- test2() -- fails. I am not even sure if my effort is a good approach or not, but I know it doesn't work!
Basically, I am looking for how to assign the range of values to an array when I am determining the range in code -- as in:
After
Set Range = ~~
or,
Range(Cells(1,1),Cells(x,y)) ~~................
I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require
View 3 Replies View RelatedI have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies View RelatedI am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies View RelatedHow can I conditionally total based on a Lookup?
e.g.
Column A shows the item code.
Column B shows the age of stock items as 1, 2 or 3 (years).
Column C has the cost of the stock items.
I want to total the costs for stock items (column C) according to age, so that the totals at the bottom would look something like:
Total stock value for year 1: $14,537.10
Total stock value for year 2: $7,657.70
Total stock value for year 3: $1,773.00
How could I do a lookup on the following accounts but I want to ignore all the numbers and spaces. For example, in another sheet, I just have ACCRUED PTO and I want to do a vlookup on 601190 601190 5007 ACCRUED PTO.
407200 3044 UPR-STAT SELLER COV F.R.
407300 3047 UPR-GP/ST SELLER COV F.R.
407200 3040 UPR-STAT BUYERS
601190 601190 5007 ACCRUED PTO
601150 601150 5033 WAGES OVERTIME
I'm currently working on a side project for work that I believe will save many man hours as at the moment we're using paper and calculators!!! I'm scheduling buys. I've worked out formulas and formats to show how much we should buy and how they should be split (for example for UK purchases we buy x amount and split it 10% 90%). However, the dates of delivery are different. See below:
PORT L'poolPORT HKPORT Portsmouth
1.113-Aug-1206-Aug-1230-Jul-12
1.210-Sep-1203-Sep-1227-Aug-12
2.103-Sep-1227-Aug-1220-Aug-12
2.201-Oct-1224-Sep-1217-Sep-12
3.124-Sep-1217-Sep-1210-Sep-12
3.222-Oct-1215-Oct-1208-Oct-12
Campaign 1 first buy (1.1) I know that my product is sailing from port HK. Therefore I want it that in the 'delivery date' cell, it returns 6/aug/12 (that's the first lot of 10%, and underneath it returns 3/sep/12) which the remaining 90%...2.2).
However, both the campaign number AND port options are variable (using a data validation drop down list). In effect meaning if I choose campaign 2 instead of campaign one, it'll return the dates 27/aug and 24 sep....if I then change the port to Portsmouth, the dates will be 20 aug and 17 sep.
I've attached a quick view of the schedule I've created. The yellow cells are where I want the dates to be returned.
ScheduleEX.jpg
I'm trying to use a state transition matrix in Excel. I randomly generate a
percentage and want to lookup the value in a corresponding table where this value would fall if we were to sum across the row. For example, the first randomly generated value is 13.5% and we are starting in state 6. Therefore, I want a formula that looks in row 6 (starting state) and determines which column (1-6) has the value 13.5% if you sum the values across (in this case the answer is 5). Then 5 becomes the new starting state and so on. Is there a formula or combination of formulas in Excel that can carry out this complex function?
State transition matrix
123456
185.00%10.50%3.10%0.74%0.66%0.00%
28.90%79.00%9.20%1.70%1.20%0.00%
31.00%7.50%73.00%15.00%2.50%1.00%
40.00%1.00%13.00%76.00%9.00%1.00%
50.00%2.00%5.00%6.00%71.00%16.00%
60.00%1.00%1.00%6.00%9.75%82.00%
I have a master table that has a list of Product Names in column A and the associated product rollup names in column B.
Next, I have a data table that has a list of company names, Product Names (the names are all included in the master list of Product Names), Transaction Type (Sale, Cancel) and Value.
Lastly, I have a data summary template that I built where I want to be able to calculate total values based on the data table and the product rollup from the master table.
I would like to build formulas into the summary template where it looks at the Product Name column from the data table and finds the associated Product Rollup from the master table and then sums all the values for Transaction Type Sale and then sums up all the values for Transaction Type Cancel. The end result would be a Sales Value for Product Rollup 1, a Sales Value for Product Rollup 2, a Cancel Value for Product Rollup 1, a Cancel Value for Product Rollup 2, etc.
I have 2 sheets, First is the Master that contains 2 columns (Item Code and Item Name), The second also contains the transactions for item codes and item names.
I can check all the item names that it's entered according its code using Vlookup for every row...My question: Is there an array formula (one formula) to check that all Item names is correct according to its code in Sheet1?
Here what I want to do.
I have 2 columns A and C.
The A column displays the winning teams every week and column C displays the losing teams each week.
our league has 2 divisions. Now during the regular season schedule we go head to head against our own division and against the other division.
What I would like to do is keep track of win loss record against each division and against the others.
So for exampls if the Cardianls win against the Angels the Cardinals would be in column A and the Angels in column C. The Angels are in our division. (So are the Braves, White Sox, A's). And if the Cardinals beat the Yankees, the Cardinals are again in the A column and the Yankees are in the C column. In the other division are Blue Jays, Mets, Red Sox and Cubs. From that I would like to see the result of NL 1-0 and AL 1-0.
Let me know if you need more info.
Sheet 1 has a data entry sheet - with a list of Local Authorities down the left, and criteria against which they are scored along the top. They either score, 1, 2, 0, or 'Unknown.' The order may be changed through sorting.
Sheet 2 is a summary, and I need to count how many 'unknowns' there are for each line.
I can't figure it out. And I am sure it is dead easy. In my defense I have been in bed ill for a week, and my brain isn't firing on all cylinders.
I need a formula that combines sumproduct and vlookup. Basically i have a speadsheet with staff names and hours worked in two columns. I need to lookup the staff names to return their cost rates and multiply it by the hours for each individual. However, there may be 20 members of staff so don't want to have to do a formula for each person.
View 3 Replies View RelatedI have a player list, from which i have put a sample below:
ALL ROUNDERS W1 W2 W3 W4
Garry Adamson ar 23 2 45 12
Charlie Baxter ar 10 0 23 14
Iqtiwar Chaudray ar 0 0 1 2
Hugh* Doulton ar 0 5 12 10
On the left is the player name and then to the right are their scores for the first 4 weeks. Each team that has entered the league has a tab of its own with the 11 players they have selected on. I would like to just have to update the main sheet and then have a look up in the other tabs on an if the player is ion this squad basis... I have attached an example of the whole player list and an example of a team on the 2nd tab.
I have a spreadsheet with a medium-sized data set, and I need to look up values from the large table, and thin the table down to only the relevant values.
To arrive at each unique value, I will have to match 3 input cells to the first 3 cells of the data table, then copy values from across that specific line to the appropriate cells of my smaller table. I really don't need to sum or do any math in this step, its just a copy from the master table to the smaller table, the math will come later.
I think I'm attaching the file (remember-first post) so hopefully it will help with what I'm trying to accomplish. Hopefully this isn't a big deal, but I've been pulling my hair out on this all day and I just can't find a solution anywhere that really matches up to this.
Without using helper columns, what is the best way to return a value from an array using three criteria from irregular data? The attached sample gives and example.
View 10 Replies View RelatedIf you look at my attached example. I'd like the date line/columns in Sheet "8002372008" to be populated automatically with data from Sheet "Data".
Is this possible? VLOOKUPs and MATCH are what I was thinking, but I'm not sure how to do it.
I know it could probably be done in VB, but I don't want to have to click on each "800 Number" sheet to update it every time, so that my summary sheets are accurate.
On sheet1 I have a column with multiple names. I need a function that can
look up all those names from column B of sheet2, and then count the occurance
of a value in column H of sheet2. Here's the example:
Sheet1 (note that some rows are blank, and some names may appear >1 time):
Helen Back
Cyndi Lou Who
Cyndi Lou Who
Sam Spade
Sheet2 (names only appear once):
col Bcol H
Cyndi Lou WhoR2
Dan DangerR1
Dou LuvaR3
Hal BrookR2
Helen BackR1
Joe MamaR2
Lou DuvaR2
Mai TaiR2
Roberta FlackR3
Ron PopeilR2
Sam SpadeR2
Will NotR2
Yodan/a
I need a formula that can do lookup sheet1's names in col B of sheet2,
then give me a count of all values "<>R1" in col H. If names are repeated in
sheet1, I do want to include the multiple occurences in my count.
I run a store, and currently we handwrite all of our reciepts - and then implement them into a "Sales Sheet" on Microsoft Excel. From there we can monitor monthly sales and targets - coincide what we are selling, and figure sellthroughs etc etc.
To make life easier I am looking at typing out reciepts in Excel - within the same workbook as the "Sales Sheet". I would save each reciept as a separate sheet, and would like to know if it is possible to somehow copy data from the "Reciept" worksheet to the "Sales Sheet" worksheet??
Some notes on my example file:
The data would get pasted into the first sheet, in the shaded area. It might be exactly those IDs, or others - possibly a longer or shorter list each week.
Sheet 2 has the lookup master lists, or checklists. I can set these up ahead of time, and don't change week to week. The input IDs will be checked against these lists somehow on the Report pages.
Sheet 3 and Sheet 4 are the Report pages.
I want a formula in column A to match the input ID values from Sheet 1 to their appropriate checklist on Sheet 2, and return the ID value on the appropriate Report. If the value matches Checklist 1, it should repoirt on Report 1. If it matches Checklist 2, it should report on Report 2. If the value isn't on the checklist, it shouldn't report, don't report it. If there are ID values on the checklist that don't appear on the input, they also should not report.
On the Report pages, once I have the correct IDs reporting, I can put in other formulas to pull Revenue, Cost, etc. info from elsewhere, VLOOKUP-ed from the IDs. (I know how to do that.)
The key is that in terms of sorting out the correct ID values to the correct reports, I don't want to have to do anything to the Report pages each week (like sorting to get rid of blanks, etc.) - I just want to paste in the Input.
I have monthly revenue information going across columns identified by an account number on sheet 2. On sheet 1, I have an order date alongside the account number. I need a formula that will return total revenues (on sheet 2) for all that occured before the order date, and all that occured after the order date.
I have attached an example here...the two yellow boxes are where I am looking to place the formulas on sheet 1.
I just reinstalled windows (XP) and Office 2003, SP3.
When entering text in a column and if it was larger than the current column, it used to just display into the next column unless there was data in that column.
Now, it just displays a few letter from the end of the first word. How can I change this back as default?
I am submitting a book CSV file to my website. However, the website will not accept any cell that contains more than 80 characters. The error states the following. "WARNING: Title should be less than or equal to 80 chars; your Title was truncated to 80 chars" repeated 13 times.
View 2 Replies View Relatedthis is a hard nut I've been working on for a while. I have a worksheet that in column A has the following rows:
| A
1 | 21432 - Accessories
2 | SortPrice
3 | 1
4 | 2
5 | 3
6 | 4
7 | 5
8 | 6
9 | 21440 - Furniture
10| SortPrice
11| 1
12| 2
and so on...
What I'm trying to do, is to make a macro that will fill the value "21432 - Accessories" down until the next which is "21440 - Furniture", which then should be filled down until the next 5 digit value..
In my opinion the best way to approach it would be to search for a string longer than say 10 characters, then fill it down until the next string that's longer than 10 characters..
This is what I'd like it to look like after the macro has been run:
| A
1 | 21432 - Accessories
2 | 21432 - Accessories
3 | 21432 - Accessories
4 | 21432 - Accessories
5 | 21432 - Accessories
6 | 21432 - Accessories
7 | 21432 - Accessories
8 | 21432 - Accessories
9 | 21440 - Furniture
10| 21440 - Furniture
11| 21440 - Furniture
12| 21440 - Furniture
and so on...