Using Lookups & IF Queries Together?
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
ADVERTISEMENT
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
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
Jan 8, 2009
Trying to write an IF query looking for multiple returns that would equal the same result.
I have a series of number where the last digit would result in the same return if the digit were 1 or 6, 2 or 7, 3 or 8, etc.
1 or 6 = 1
2 or 7 = 2
I have been trying unsuccessfully to combine the IF and OR queries
View 12 Replies
View Related
Apr 28, 2009
I have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.
As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.
I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?
View 4 Replies
View Related
Jul 31, 2007
The report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SET ROWCOUNT 100
SET NOCOUNT OFF
SELECT EventTagName = EventHistory.TagName, Value
FROM EventHistory
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey
WHERE SnapshotTag.TagName IN ('SysSpaceMain')
AND DateTime >= @StartDate
AND DateTime
View 9 Replies
View Related
Aug 8, 2006
I'm trying to produce a spreadsheet that pulls infomation from different web pages throughout the day. It needs to pull quite a large amount of information from different web pages hence I wanted to produce a subroutine which could be called with different variables for each web page, i.e. url, field, destination, etc.
To actually produce the query I recorded a macro and then used the code it produced. I then changed the URL to url, a string variable which holds the urls of the pages. The destion address was changed to destination, a range variable to store the destionation location, and the webTable was changed to table also a string variable to store the table number. This is shown below.
With ActiveSheet.QueryTables.Add(Connection:= _
url, Destination:= destination)
.Name = "q?s=%5EFTSE&m=L_137"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True.............................
View 2 Replies
View Related
Mar 30, 2007
I want know how to pass a parameter through a cell for date in the following url
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=10
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
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
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
Dec 10, 2008
My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )
I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.
View 7 Replies
View Related
Jan 19, 2013
i would like to create reports and queries in excel just like in access is this possible or do you import a layout,i have a sheet that has data on i would like to be able to pick just one row or mutiple rows and print them off could i save them to a menu to use over and over again,would i use a userform to search for these or can it be done otherways ,have seen pivot tables but seem bit complicated
View 2 Replies
View Related
Mar 20, 2014
I'm new to macro and have tried copy from others who have posted before but it don't seem to lead to anywhere.
I'm trying to automated a column of web query (sheet1,column A), a batch of listing site with no tables. with the results in sheet2.
Looping part as well. Also, is it possible to just extract <title> from the page source.
Here's what I have so far.
PHP Code:
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Sheets("Sheet1").Range("A2") _
, Destination:=Range("B2"))
[Code] ........
Tried changing the destination from
Destination:=Range("B2")) to Destination:=Range("Sheet2!$A$2"))
But keep getting error to change destination.
View 1 Replies
View Related
Apr 1, 2008
I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.
What bugs me is EDITING data.
If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!
Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?
View 9 Replies
View Related
Apr 7, 2009
i have 306 web pages listed in sheet 1, A1:A306
how can i get a macro to open each and scrape a piece of info from each, and then store in a row in sheet 2?
View 9 Replies
View Related
Oct 3, 2009
I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?
Right now the copying happens like this:
If Not rsData.EOF Then
rngTarget.CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If
So should I do
1) One query and move everything in the right places
2) Multiple queries
3) Something else
Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000
View 9 Replies
View Related
Feb 10, 2010
how would you compile a web page to which there are many many querys?
i.e a website that has links to results
View 9 Replies
View Related
Jun 8, 2006
Hello folks! Here's to hoping I can eventually be a source of information rather than questions. But enough about me - on to the topic at hand.
I have a single-source web query that I'd like to expand.
Here's what I have in the IQY file:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ...
View 9 Replies
View Related