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 have workbooks based on the date. Here is an example "Daily Numbers Report - Summary_2014_02-18-06-02-30"
All of the files are formatted this way. Then, in a master file I have dates going horizontally for the entire year in the following format Tue Jan 15, etc. How could I do a lookup that would grab from all of the open files and match the date BUT subtract 1 day from that date for all the lookups, so Feb 18 would look at 2-17?
Sample data:
1234
234
123
0304
304
7685
0123
0076
687
I need to be able to use formulas on the above numbers to perform look-ups and comparisons(=IF(A1>1000,"Yay","Boo")). My issue is the leading 0. To use formulas the above fields HAVE to be in a number format. Converting to typical number format removes the leading 0. All of the help files I can find show how to preserve the leading 0 either require a set number of 0s or require a set length. Unfortunately my data cannot match those rules. Is there any way I can convert these cells to a number format AND preserve any existing leading 0(s)?
I am having a little difficulty getting my vba to correspond to what i want it to do. I have columns that can vary with how many rows they have weekly. The all start at P2, R2, V2.
These columns always have the same number of rows as each other, but the number of rows varies.
I compare my R2>P2, and if it is return the number in R2. If not, i am looking to take P2 and round down to the nearest .95. (ex: R2 is at 3.45. The value that should be put in in V2 is 2.95. Since the values all change as well, it needs to compare: .96-1.95, 1.96-2.95, etc. all the way up to 15,000.
I know it can be done with if's, whiles, or from i to x, but my experience is in vb.net, not vba. And, since i am new at this, i can write the code, but don't know how to make it correspond to a new excel filename weekly.
I have a sample of aproxomatly 30,000 records, and I am working to do some data analysis on it.
I am comparing multiple fields using sumproduct(), but if I go above two criteria, the calculation time becomes incredibly long. Is there a faster way to preform these large comparisons, or am I stuck watching my Excel lag out for 5 min every time I recalculate the sheet?
With the following procedure, I am using a Part ID number to look up that part's location in a worksheet. To do this, I am using the DGET function which now works, thanks to all who helped with those issues.
But now I'm trying to harness the outcomes from that function.
--It displays #VALUE! if the part ID number was not found in my worksheet and therefore the function can't return a location.
--It displays # NUM! if the part ID number was found in multiple records/rows in my worksheet and so it has multiple corresponding locations.
I am trying to construct an IF section that will clarify what these mean to anyone running this macro. I want to replace #VALUE! with "Not Found" and #NUM! with "Numerous".
The issue arises when I compare the result of the function with the #VALUE! or #NUM! strings. I've tried StrComp and a simple equals sign (as below) I've even tried having Option Compare at the top, though I'm not familiar with how that would change things. But I am getting "Type Mismatch Error 13" all the time. I also get that error when set my DGET function = to a variable (Dim'ed as string)
Variables to point out:
---Sheeti is a Publically defined Worksheet
---'CriteriaRange : Publically defined Range that is empty (until assigned criteria for my DGET function later within my loop)
---'InventoryRange : Pubically defined Range that contains inventory data that I look through with my DGET function (Items in the inventory are identifyable by a string I call "sItem" and each has a location in a corresponding column that I am looking for with my DGET function)
Public Sub LoopThruParts()
Dim y As Integer
Dim sItem As String
Dim ValueError As String
Dim NumError As String
Looking for any assistance: Excel 2000 (9.0.6923 SP-3) Windows XP Pro SP-2
I am trying to get Excel to sort through data in various cells,(text and numerical) and do comparisons with a positive/negative or yes/no error displayed at the end of each row.Suggestions so far range from don't know to use Arrays, I am swamped in MSN help pages and am completely lost now. here goes:
In the example below I want Excel to compare Column P4 against Column M4 and display a result as above, so for instance British Midland (M4) will always be 40100350(P4) & Heathrow Express will always be 40100355, British Rail (Tickets) will always be 40100355,so I want Excel to compare all Suppliers against their corresponding codes for each row Eg (P4 to compare to M4) and display if this is correct or not, the codes 40100350,355 etc do not change ....
How 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.
If you look at the workbook I have attached, I have made a mock up of a file that I have to use. Cells A1:F12 are part a "Situation Status" file. My team uses this file to update that status of certain events. It shows the current planned estimated completion date for each event in each area. We are required to strikethrough every time the estimated completion date slides out. In some cases, the dates have move 3 or more times so there are cells with multiple dates with strikethroughs. The current date is always the last date in the text string and does not have a strikethrough.
Cells H1:J17 are part of a matrix that I am making to try and capture only the most current ECDs. I want I3 to show "8/10" instead of "8/1 8/3 8/10."
The Event Status and Event Matrix are part of two separate files that I have. The Event Status file is constantly changing so I can't simply say =RIGHT(B4,4) because I could wind up showing the date of a different situation.
Is there a way to lookup the situation name (i.e. "Situation 1"), the Area number, and the event number and return ONLY the latest ECD (the date without the strikethrouh)?
I am looking for creating a dynamic meeting log/calendar for my team. There's a couple of elements to my questions below:
I have a log with columns such as "Meeting Date", "Client", "Attendee" and have also set up month calendars on separate tabs which automatically update the dates according to a "Year" toggle using Janoffset, Feboffset, etc.
What I would like to do is have these calendars automatically populate a cell beneath each date with any meetings on that day. The client name and then a space then the attendee in brackets would be sufficient and with each meeting having its own line in the cell).
I have gotten a Vlookup working but have run into two snags: 1) where there is more than one meeting on a single day 2) I can only return one piece of information in the cell or otherwise have it blank, I can't have the client name AND attendee.
Is there any way to lookup all of that information in the one cell or is this just a pipe dream?
I have attached my workbook which contains my workings so far and in the first cell of Jan shown the "ideal" format. I'd like the data in.
Can you get a VLookup to report the first value it encounters but then continue and report all other instances.
I am only aware Vlookup searches a range until it finds the first instance.
e.g.
A Apples
B Pears
C Grapes
D Oranges
A Insects
C Animals
A Bugs
B Blue
A vlookup of "A" would return Apples and I want it to return in column 1 Apples column 2 Insects and column 3 bugs.
The data is all mixed up and cannot be sorted so I could sort into blocks to change the lookup range.
I'm having is with different Standards there are different names for the beam sizes - so i need validation in a single cell over mulitple tables. Also i then need to perform the lookup function ( or maybe this isn't the correct way) over the mulitple tables.
The attached spreadsheet will illustrate this better.
I am not too familiar with the advanced excel functions, but should be able to easily grasp these with an example or some guidance.
I have a table which contains information about stock, item description, price, barcode, stock qty etc.
I have used lookups to make an invoice so I can sell the product and print a receipt etc. I want to be able to take away the amount sold off the specific product that was found using the barcode and a lookup and take that amount from the stock qty column in the lookup table.
Is there a way in which this can be done? If so how?
I have included an example of the invoice and the lookup table that I want the stock to be taken from.
I have a tab that has rows with line items from a financial report (the items start with unique identifier numbers) and the tab also has historical monthly data for each of these items. I now have a new tab with the same line items but they are now in a different order. Is there a formula that I can use to copy all of the data from the old tab into the corresponding monthly line item in the new tab? Nothing changes except for the order of the rows.
Also, at the top of the old tab, there are totals that grab from each of the rows below. If I copy these formulas into the new tab, I think that they will reference the incorrect rows (as the order has changed) Is there any way to transfer these formulas and keep the linkage intact?
I am building a dashboard for a project, which is going to be on a separate worksheet for easy printing and providing only needed data from my database. I have attached a small example in the format I will actually be using as I think the formula is going to be specific to my dashboard.
Attached file, you should see a column "Rank" highlighted orange, with certain rows being numbered. My objective is to display certain data from that particular row, and display it (scroll to the right to see the dashboard display layout) on the dashboard. I think my main goal is to have the specified data displayed in ranked order 1-10 from the top down, and I don't know where to start with that....the other possiblity is to just have the specified data display in ascending order of the zip codes as long as something other than <blank> is in the "Rank" field.
Example.xlsx