I know we have the V and H lookup functions but... what I had invisaged for my colleagues sheet to do was to have excel look down the column (V) and at the same time across the rows (H) to return a value but in the same formula (a bit like school times table grid squares).
i.e. for 7 * 8 it would scan the left most column for the number 7 then at the same time, pan the first row for the number 8 and see where they met (for standard simpleness, column A is labelled 1-10 and row 1 is labelled 1-10, so the result for 7*8 would be in cell H7)
I have a large amount of raw data that I import via a text file into Excel. Here is an example of the data in order for me to illustrate my question.
USD COUPON ENTRY PREVIOUSLY DELAYED S/N 0243**** dfssdfsdf S 9.37500 07/02/11 USD REC. 23/07/07 DUE 07/08/07 CPN.AMNT USD 23.437500 Q NAR. FUNDS RECEIVED
SCA. USD 10860000 ----------------------------------------------------- NET USD 254531.25+ 07/08/07 ----------------------------------------------------- DACE REFERENCE IS *******
On a seperate workbook I have a list of money amounts which I need to find the S/N # for. So if you look above, there is a money amount and a S/N within the raw data. My question is, what can I do to take the money amount from the first workbook and search for that amount in the raw data, then return the S/N number that is within that same area? And do it via a macro or advanced formula so that I can do I many times over?
Keep in mind, even when I delimit the data, it's all in different columns and rows, and never consistently spaced.
I have been struggling with what seemed at first like a very simple formula. I have the sample data as shown below and I am trying to work out a formula that provides the latest value in sheet 2 from sheet 1 column AF based on a number of conditions.
If Sheet2 A1:A500 = Sheet1 F1:F500 And Sheet1 G1:G500 = max & Sheet1 AF1:AF500 doesn't = blank (i.e. last value in column AF) Then return adjacent value from Sheet1 AF1:AF500
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run. I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like: AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like: ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _ Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery) .RowNumbers = True .Refresh BackgroundQuery:=False iResultRowCount = .ResultRange.Rows.Count End With When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
hello honorable excel profesionals. here is what i want the macro to do:
must start from the top of the colum and count cells with specific data in them:
if ''X'' or ''Xa'' or ''Xb'' -> start counting how many cells in a row have the value specified. from here two things can happen:
if a cel with a value ''Y'' is met -> stop if a cell with value '''Ya'' or '''Yb'' is met -> countinue counting untill Y is met
when I say stop I mean that the macro should imput, ona separete table: 1.total number of how many counts where made. 2.how many counts there where with a lenght of 1, how many with the lenght of 2 and so on
here is an example of data line and a table I want to have after macro is done.
Hope that this isn,t a problem to write otherwise il have to do this manualy - about 5000 rows of data...
I have a difficult one I think? I have a workbook in Sheet 2 I have a page with data.
I would like to create a Sheet 3 that would look at Sheet 2 and copy all rows (Range A to V) with the asterisk displayed in Column B... IF MORE THAN 30DAYS AFTER THE DATE ENTERED IN "Column C"....
Assume cell A1 is "ageioslop315555555138lkeameox". Is there a formula that I could use that could match 31555555138, and if it matches put the 31555555138 in cell A2. You can probably see where I am going with this, there is several thousand lines that has sporadic descriptions that I need to find out which ones have certain #'s that correspond with info. on our end.
with a complicated average function. I’ve made a tender comparison sheet which compares different suppliers and their prices. The suppliers are assigned a region (with data validation when they are added and I want to get an average on the specific article (on every row) on all suppliers in the same region.
I wish to perform a VLOOKUP on my spreadsheet. I have my table to be called upon all setup and can get the VLOOKUP to work, but I need some help finishing it.
I want the VLOOKUP to take the information from A1 AND C1. Can I do this in a VLOOKUP?
So instead of the VLOOKUP just taking the Lookup_value from just A1 or C1, I want it to match BOTH before it shows me the result.
I am working on a large Audit project for one of my many bosses and I am a little stumped so I came to the experts. I have a list that is organized randomly and we need to have 95% of the cells deleted. I don't want to have to go through each time and calculate that out each time.
I am sure that I would have to find the last cell used in one of the columns and then multiply that by 95% and then delete from that row down in the sheet. (Since the sheet is already organized randomly it can delete the bottom 95% of rows.) I am not sure how to put it all together.
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James Wilkonson, Paul, etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
I have a spreadsheet that currently includes the following formula:
=VLOOKUP($C42,$A$42:$B$61,2,FALSE)
Col A is numeric output Col B is staff names Col C specifies top ten output numbers from Col A
The above formula goes in column D and It returns the staff names with the top ten output. My problem is that if two members of staff have the same output it formula will repeat the name only one staff member in both rows. Is there any way of altering the above formula so that it will show both staff names in each row?
My excel workbook has 3 sheets, payments, invoice data and invoice. Payments and Invoice Data contain a table that has been exported from access, I have used an advanced filter macro, to filter records based on payment id, the button for this macro is on the "invoice data" sheet, the same button will filter records even on the "payments" sheet. You will notice in the payment sheet, the total payment field is blank, i used cell referencing to equate the cell C21 in the payment sheet to the cell H20 in the invoice sheet, so basically, once the sort button is clicked and advanced filter is done, the total payment will show in cell C21 in the payment sheet. I would like to create a button that runs a macro that will enable me to save the data i have clicked on cell C21 on the table above it, so i could import that table back to access.
I am desperately seeking a better way of transposing a large amount of data. By the looks of other posts, my objective is possible with a bit of VBA. Sadly, I am VBA illiterate.
My data looks like this (simplified) [ data is separated by columns "|".
A | May 1 | Jun 25 | Aug 9 | Dec 12 B | Apr 1 | Oct 25 C | Jan 6 | July 7 | Nov 11
I want to make it look like this: A | May 1 A | June 25 A | Aug 9 A | Dec 12 B | Apr 1 B | Oct 25 C | Jan 6 C | Jul 7 C | Nov 11
I have accomplished this in the past, but it involved importing the table into access, then creating a query for each of the "date" columns, then copying and pasting the results into one table. It was a painful experience considering there are over 2,000 lines on the original table (resulting in 25,00 lines in the combined query).
I have a file that has a column of dates. I would like to use advanced filter to filter anything with a date that is <today()-1 but I can't seem to make the fomula work. I can make it filter on =today()-1 but when I use the < it doesn't calculate today's date and gives me nothing.
I've been looking around for a way to use the countif function in excel with a wildcard, I have a excel list with a column containing data which looks like: ....
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
why the Advanced Filter works in the macro but not in the UDF?
Code: Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name Dim c As Range rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
At my job, employees accrue X hours vacation per 2 weeks worked. This makes it difficult for everyone to plan vacation (IE - will I have enough vacation in January to go on vacation for 2 weeks?) I'm trying to create a calendar in my spare time to help out, but it's turning out to be quite a bigger task than I've ever attempted in excel. Therefore, I'm coming here hoping someone will point me in the right direction.
I found this: [url] It does the majority of what I need. However, as it costs money, I'm afraid they've locked down editing of things and I won't be able to add functionality to it. It'd be annoying paying them and then finding out I can't add vacation days earned, as the spreadsheet is locked.
Features I'd like added to the above program: - Add another checkbox (or similar) to show hours of vacation accrued, as well as an original date and hours to start counting from. - Highlight days corresponding to what kind of day it is. Weekends = red, holidays = green, vacation = yellow, etc.
What I started with: [url] This gives me a calendar with the dates in the right place. I've created macros to identify holidays dates. It's starting to get pretty annoying using conditional formatting on everything to highlight days. Especially since I have to do it for each of the 12 months, as it has a hard-coded month cell in there.
I have a spreadsheet where a charity has a list of donors (about 500 rows) and the amount given over a number of years (1999-2007 columns B to I). Say for example, one donor gives in years 2000, 2002, 2003, 2006 and 2007 I need the conditional formatting to realise what number gift it is and apply the appropriate colour to the cell with the value in it (e.g. 1st donation white background, 2nd donation yellow background, 3rd green, 4th orange, 5th purple). Obviously different companies donate in different years so the code needs to apply to the whole sheet when the donations occur.
