As the subject states I am having trouble pulling in a text field from a database. I need to pull it based on 2 criteria (date and workcenter). I tried using a nested MATCH function with the INDEX function, but with no success. The problem is that there will be multiple rows in the database that meet both of the date and workcenter criteria.
Nevertheless, the data in the column I am trying to retrieve is consistent and would add value if it could be pulled into a cell via a formula.
If the index/match formula operated like a VLOOKUP and would pull the data in the first match found I would be good to go.
I have a two dimensional database from which I need to pull information based on multiple listbox values (these listboxes are located in a userform). The user selects one or more cars and then chooses from various parameters for that car. The output has to show the results - for example, if Jeep Cherokee and mpg are chosen, the output will be 23 mpg. I need to be able to do this for multiple cars and multiple parameters.
I need for the outputs to be located in a worksheet. I already have the code for setting up the list of cars and parameters selected (this gives me a row and column header), but now I need to be able to cross reference this information to pull the output from the master database. I was wondering if I can use a vlookup function to do this, but I've never used it with 2 dimensional data.
My data consist of multiple entries of same id(s) at various point of time. I need to extract the latest data of a particular ID. I was using vlookup but it extract the first entry of that ID but not the latest. and the data is entered into the database using userform and VBA macros.
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
i have a long list of what were once file names in excel that i need to retrieve data from. i have attached an example file with 2 file names which i recomend to view while reading this request.the file has 2 spreadsheets. the first one is just the file name in the format in which i recieve it. the second one is a table that i need to fill out from the data in those file names.
i have a problem with the following columns in spreadsheet 2:
1. column C: i have the command to copy the site name as it is to this column, but what i need is for the program to read if the site name is ZANUAH or ADORA and then write only Z or A. note that these two site names have a different number of charecters in them.
2. columnd D: similar problem. i need it to read the lab name and write AL if its MAGAMA, BA if its Ben-Ari and SH if its shafir. i have the command for excel to simply copy the word, but how do i make it write the letters that represent the lab name rather than the lab name itself?
3. column E: the report number is the 6 digit number in the file name. i have the command which retrieves it, but it has trouble when the length of the number changes. its important to note that sometimes the number might contain non-numerical characters like 219641-1.
4. column O:i have the command to get the data from the parentheses next to PSD in the file name into a box. what i need is to get it to copy just the letters C or NC from the file name into this column, without the number.
5. column P: same as column O, but here i need just the number, without the C or NC.
6. column T: all i need is for it to copy the last 2 letters from the file name, which i know how to do. the problem is that since the file names come with a .pdf at the end, all i get is df. so in fact i need it to copy th 6th and the 5th letters from the end of the file name, which is above my abilities.
I have already added the developer tab to excel, but under insert the last three options under Form controls (Text Field, Combo List - Edit, Combo Drop-Down) are shaded out.
I played an online simulation game a few months back that was driven by Excel. In the simulation game interface were screens and on these screens, the players of the game could enter values into boxes on the screen. When the user pressed Enter, it would update the simulation game with your choice and if you wanted, could immediately type in a different value and press enter.
My point is, like in a normal Excel spreadsheet, when you press enter, it goes to the next cell below. In this game, the (whatever it's called... active cell?) would stay in one cell when Enter was pressed.
Is this a text field? I've looked all over on how to put a text field in a specific cell. I've went to Developer tab and on the Insert the Text Field (Form Control) is unable to be selected so I guess my question is, how do I add a Text field to a cell if it's possible? Very similar to a Search box. When I enter alphanumeric characters in the text field (cell) and press enter, it activates a button which runs my macro.
I am working on making a time sheet log that will have employee name, 7 rows for days of the week and a row for total hours worked. each cell will either be blank in that row because employee has not worked that day or it will have numerical hours and the place the employee worked. We have many different job sites so text is subject to change. For example Monday employee 1 worked 8 hours at MRCR, so cell would state 8 MRCR Tuesday did not work so left blank. Wednesday worked 8 hours at CCR so will be 8 CCR. Thursday worked 8 hours at CU. so 8 CU in cell. Friday Saturday and Sunday left blank. How do i add create a formula that would be standard formula for each week for employee that would add numbers and omit text as well as ignore blank cells from week to week as the employee schedule changes.
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database A B C D E F G 10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I am trying to display text in the value field within a pivot table. I do not want a count or any number to display. What I am basically trying to create is a weekly schedule for nurses, with the dates on the top and times along the side, with "call type" and client as the text data to display with the value field.
I have attached an excel file with all the data on Sheet 1 and my attempt at a pivot table on Sheet 2. Perhaps there is some way to write a macro to display the data in a similar fashion, without using a pivot table?
Having difficulty with a VLOOKUP that uses a text lookup field beginning with 0. For all the other numbers formatted as text I use this function:
[Code].....
For the one that begins with a 0 I have to use this function:
[Code] .....
This is a problem for me, because I need to be able to copy the top function to all of my sheets without having to edit the ones where the lookup field begins with 0. I could alter my VBA to specifically search for those cases and adjust, but that seems unneccesary.
More info - The beginning column of the Table Array (i.e. the lookup field I'm searching for) is a function of another cell; both cells are formatted as text. Here is the function:
[Code] ....
I have to put a single quote in front of the lookup field, so that "064" is not displayed as "64" even if the cell is formatted as text. I have another instance where I am looking up "'791" and it works fine with the first VLOOKUP above. Even if I remove the single quote this will work. However, once there is a leading zero the function no longer works and I must use the second VLOOKUP listed above.
I am looking for a solution to split text from numbers.I have found a couple examples on the web but I cannot get the examples to work with the correct syntax to function.
Sample cells. The string could be any integer or floating point number with text. (The text is always after the number.)
I need excluding text from a cell and come out with a random number up to 3 numbers. Please see the attached image;
If you notice in column D there is text and a number that starts with a decimal, for example on the first row is .437 the next row is .215 next row is .63 etc... Is there a way to come out with a random number mixing it up to 3 digits and output those number on column "E"? Im only interested in filtering and coming out with the random number i mentioned .437 .215 .63 etc... not the +/-.010in.
There is no right or wrong number, i just need to come out with random number using up to 3 numbers up.
Maybe for .437 three numbers up could be .440 for the next row .215 maybe a number up .216..etc..
Hope there is a way to do this, my excel file is too long to type a number manually.
So i have a raw data dump that I need to put in a spacific reporting fashion. Excel pivot tables seems to almost do the trick. The problem is the information that I need to drop in the "data" field is text (peoples usernames) and when I drop it in I get numbers instead of names, and when I drill down on the number then I get the exact format I need but only for that line... Any ideas? I am using Excel 2000
If pivot tables will not work does anybody know of another was to get raw data in a mapping type of spreadsheet?
I import a database with mostly unique records which has a description field that includes, among other things: Model Name, followed by predominant color. Unfortunately, there are no seperate fields for the Model Name or Color, e.g.:
DESCRIPTION 8070 CALLING WATER RED 508001 EYEDAZZLER LILIANA CARAMEL 502034 EYEDAZZLER LILIANA RED #402022 DIAMONDS IN THE DARK RED 402024 DIAMONDS IN THE DARK PALOMINO
Is there a way to create a summary table which would count the number of occurences of the Model Name and the number of occurences of the color?
Fortunately, there are no more than 25 Model Names, and no more than 10 colors or color/combinations, so we could compare against two lists.
I am struggling with data field in pivot table. Is it not possible to use strings in data field?
Here is a problem :-
Col1-----------Col2-----------Col3 ---------------------------------- A 11/1/2007 Y A 11/2/2007 Y B 11/1/2007 Y B 11/2/2007 N C 11/1/2007 N C 11/2/2007 Y D 11/1/2007 N D 11/2/2007 N
Required op :-
11/1/2007 11/2/2007 A Y Y B Y N C N Y D N N
I used pivot table to generate this output but in a data field I don't get strings; instead it shows 1 in place of Y/N.
I am using excel to compare data from one spreadsheet to another. However my utility is not capturing identical matches since one worksheet eliminated the leading 0s while the next worksheet includes all 0s. Our system should actually have the 0s to identify the data correctly.
Worksheet 1 M10235 Worksheet 2 M000010235
I would like to convert Worksheet 1 to show M10235 in the format M000010235 so that the number contains the prefix M followed by nine digits.
The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.
I have a text field at the bottom of a user form that remains hidden (i.e. visible = false) until the user clicks the "Ok" button. At that point, I want the text field to appear as the macro is running (it's a large macro, so the text field just says "processing, please wait...").
Im trying to make a simple macro for cataloging some equipment and where its located.
I've created a user form where there is a free text field for Equipment. Instead of just inserting this entry into the first available cell, i would like for it to place it in the first available cell under the corresponding first letter of the equipment to the named worksheets A-Z.
I have named the worksheets A through to Z, and upon entering the free text 'Equipment' name, say for instance Trolley, for it to recognise the T, open the worksheet named T and insert the equipment name and location etc across the first free column.
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.