One Input, Two Lookups, Two Reports
Nov 13, 2006
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.
View 3 Replies
ADVERTISEMENT
Nov 3, 2006
I'd like to be able to drop one table of data into a workbook that already has sheets of two or more master tables, and two or more report pages. A formula on the report pages would compare aspects of the data to the master tables, and report the data on the correct sheet based on certain criterion. The reports would not have blank spaces, need to be sorted further, etc.
My initial impulse was to think in terms of ol' verable VLOOKUP, but I can't figure out how to get the report formula to move on to the next line of the data table if it doesn't find a match for the first line - short of adding another VLOOKUP (and another, and...), which is totally impractical given that the data table will likely have 20,000+ lines.
View 4 Replies
View Related
Jan 27, 2014
I'm lookng for a way to add functionality across tabs in a spreadsheet where if you put a date and $ amount in different cells on an expense sheet, it recognizes the date of the amount input and it shows up in another tab with a list of monthly expenses.
View 4 Replies
View Related
Feb 17, 2014
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
View 4 Replies
View Related
Aug 6, 2013
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
View 3 Replies
View Related
Mar 12, 2007
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
View 5 Replies
View Related
Apr 4, 2014
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
View 4 Replies
View Related
Dec 28, 2012
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
View 4 Replies
View Related
Mar 3, 2007
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%
View 9 Replies
View Related
May 22, 2007
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.
View 9 Replies
View Related
Jan 28, 2008
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?
View 9 Replies
View Related
May 3, 2008
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.
View 9 Replies
View Related
May 7, 2008
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.
View 9 Replies
View Related
Feb 21, 2007
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 Related
May 15, 2007
I 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.
View 9 Replies
View Related
Aug 19, 2009
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.
View 6 Replies
View Related
Sep 14, 2009
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 Related
Dec 23, 2009
If 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.
View 10 Replies
View Related
Jul 11, 2006
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.
View 11 Replies
View Related
Aug 19, 2008
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??
View 2 Replies
View Related
Jun 25, 2008
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.
View 5 Replies
View Related
Aug 20, 2014
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)?
View 7 Replies
View Related
Jun 13, 2014
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.
View 14 Replies
View Related
Jan 14, 2009
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.
View 2 Replies
View Related
Mar 31, 2009
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.
View 9 Replies
View Related
Dec 11, 2009
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.
View 10 Replies
View Related
Dec 13, 2012
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?
View 2 Replies
View Related
May 3, 2013
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
View 1 Replies
View Related
Feb 24, 2007
I have three cells, let's call them A1, A2, A3. Cell A2 contains a list of products. I need to be able to have the price for these products appear in cell A3. The prices vary based on the contents of cell A1. There is a different list of prices for each different value in A1. I've tried to figure out a way to use a lookup, but can't seem to wrap my brain around it.
Contents of A1 determines unit prices in A3.
Contents of A2 is a list of products.
Contents of A3 is pricing for list products in A2, but pricing is controlled by content of A1.
View 6 Replies
View Related
Feb 18, 2014
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?
View 5 Replies
View Related
