In Column A, in each cell in rows 1-10, there is various text that may or may not include "ABC" and "DEFG" as the first four letters. In Column B, rows 1-10, there is numerical data. What I would like to do in Column C, rows 1-10, is place a formula that would return the contents of the respective cell in Column B, but only where the first three or four letters in the respective cell in column A is "ABC" or "DEFG".
View 9 RepliesBasically, it is a scheduling of approving or rejecting of request dates. whereby, i will receive the request dates from Google doc form and then i will extract the data from web into the excel. The data i receive will include the ID, Date request and Remarks.
With the Information in hand, the data will then be "plot" into a schedule (which is a year calender that i have created on excel) base on the ID requested from the web and the ID in the excel.
After plotting, i also want to check if the number requested date exceed the quota set PER DAY, if it exceed, then i have to reject, if its not then approve.
So here comes the problem.. After all the dates have reflected on the calender, I want to get the information from the calender result(which is whether the date is approve or reject) back on to respective ID and Dates requested.
I have attached a example excel for clearer view of my explanation.
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies View RelatedBelow is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:
115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL
What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.
I have inlcuded my workbook that I use for tracking the NFL season and I'd like to add in some team logos just to make it look a little cooler.
The attached workbook includes three sheets, the first of which uses a drop-down for selecting picks. The second sheet has the team logos and the third sheet feeds off the first.
I'd like for team logos to populate into the third sheet based on what is selected in the first sheet.
I've already tried a few methods, all unsuccessfully. I tried this [URL] which was a great idea but it didn't allow for the same image to be repeated potentially 100 times. I also tried a method involving named ranges and indirect lookups to the linked picture image but that would require a different named range for every person and every pick which isn't feasible.
if it's possible to just do a massive nested if statement with a vlookup to return an image but I haven't been successful in that either.
I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.
Cell G2 will be a 4 digit number.
Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".
If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.
So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:
001-0001-0006
004-0001-0187
098-0001-0874
098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)
then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874
Also, if there are no matches, then it should just return a blank cell.
I am trying to figure out how to return the contents of last non blank cell in column B, based on the name in column A. So, if I choose "Sam", the result I am looking for is "blue". If I choose "Pete", the result I am looking for is "orange".
Sam
red
Sam
blue
Sam
Pete
orange
Sam
On Sheet2 I have a table where I want M2 to do the following:
1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.
2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"
3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.
So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.
Maybe combining the persons name in to one column would make it easier?
So right now I have a spreadsheet that looks something like this:
A
B
C
D
[Code]....
As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.
Right now I have: =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")
This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.
I am working with a worksheet that has some cells merged e.g. A1 and A2 are merged together as 1 cell. I am using cell offset to reference values from the worksheet assign them to variables then populate another worksheet with their respective values. I noticed that cell.offset(row,column).value is not retrieving a value when merged cells are mixed with non-merged cells within a given row. Is there another method/function I can take advantage of here?
View 1 Replies View RelatedI am trying to select all cells in a range whos contents match the contents of a specific cell so I can group the respective rows of these cells. I did a forum search but I am not sure if I have found what I am looking to do. I am a very basic user of VB so keep answers as simple as possible.
View 8 Replies View RelatedI am trying to do something which is very slow and manual. What i have is like a seperate spreadsheet from every day and have to save each one down manually in the respective date. so for example column C has the date in already. Can you tell me how to save all the excel workbooks which are open (if i open them all) and then use the date in column C (which is in the format 20090217) and save it as a xls in the format 170209 and then close the workbooks?
sorry this is a huge question, just takes me so long to save them all.
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies View RelatedI have the following data sets
worksheet1
TRANCODES 234568 .........99
PARTS
003-0623-00
005-0030-00
worksheet2
PARTSTRANCODE SUM_QTY
003-0018-01 28 10
003-0018-01 40 10
003-0021-00 8 25
003-0021-04 4 63
In worksheet1 I need to compare the part number and the trancode then
return the quantity from worksheet2 -
I am trying to use a SUMIF statement to sum values off of 3 seperate tabs. My issue is that the ID's used are not on each tab. Is there a way to return a ZERO if the ID is not found on the subsquent tabs?
=SUMIF(Sheet1B:B, A1, Sheet1E:E)+SUMIF(Sheet2B:B, A1, Sheet2E:E)+SUMIF(Sheet3B:B, A1, Sheet3E:E)
I have attached an example. I am wondering how I can get [K2] to return the value 501 based on the lookup.
View 7 Replies View RelatedI have attached a workbook stating my problem.
file1.xlsx
Is there a function that would return the contents of the most frequent.
i.e. in the example below with the function in cell C2 it would return Red (this been the most frequent entry in the cells A2:A10....
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
I need a code to copy the color of column B and put in the respective cell in Column A. I just need the color to copy from column B to Column A.
View 6 Replies View RelatedI'm facing a problem wherein i have two tables A& B.
Table A has two columns and 40834 records(rows).first column is userid and second column is activeyn with respective values (0 or 1) for each userid
Table B userid column is a subset of Table A and has two columns and 411 records(rows).first columns is userid and second column is activeyn with respective values (0 or 1) for each userid.
Now I want to get activeyn value from table A of all the 411 records(userid's) in Table B.
Am attaching herewith one sample file. In the column G are the number of days as at 31-12-08. I want the value corresponding to that those days to populate in respective slot of days in either column H or I or J or K. E.g. stock number e (in cell A6) is having number of days 453, hence the value 110.920 (cell E6) should fall in cell J6. However i am want the single line formula which will validate in which cell the value should fall.
View 5 Replies View RelatedTo fill in the ??? in attached file, I select the respective cells and SUM. Would there be a way to automate as:
For each entry in column D, Excel picks out itself the names in column-A where-ever they come, picks the corresponding values from column-B, sums them and reproduces the summation in column E.
Splitting numbers and text separately to their respective columns
Annexed file for reference: SPLIT TEXT AND NUMBERS.xls
Anyone can help me? I trying to give remark to my Profits and Loss amount with Poor, Good, and Excellent.
For example if my amount is more than $10000 it should be remark Excellent automatically by excel in the cell beside. How am I able to do this with conditional formatting in Excel 2007.....
Output should be:
.Column A
AB-CD/EF1-AB
AB-CD/EF1-BC
AB-CD/EF1-AB(100%)
100%
0%
AB-CD/EF1-AB(30%)
30%
0%
AB-CD/EF1-AB(4%), AB-CD/EF1-BC(2.4%)
4%
2.4%
Likewise, I have many combinations. And it is not necessary that only "AB-CD/EF1-AB" combination will come first in a row. In input,Column A values are given. Other columns are empty.
I have a problem with combobox,
when an item in a combobox is selected (control tool box combobox)
then the color of the cell in excel worksheet have to change to respective given color
Have two tables of data the first is shown below (the first line is headers)
DogCatReptileMonkey
SmallBigNosyFluffy
SmellyNosy
The second is (again first line is headings)
SmallSmellyBigNosyFluffy
AAGFF
BGHGE
CTUBQ
Need to create code which reads the first table, finds the categories of the first table in the second table and puts these with the original headings into a new table so in this example the end table would look like -
DogCatReptileMonkey
AGFF
BHGE
CUBQ
AF
GG
TB
My original has about 150 headings in table A and B hence why I have given an example
In spreadsheet attached, column F is populated with file names. I need VB script to import content of each respective file into corresponding cell in column E. I've got over 25k of records and the same amount of txt files that I need to insert into cells. I have found this script somewhere on the forums:
[Code] .....
But need to modify it to do something like this: read cell from column F > find file with corresponding name > Insert content of that text file into corresponding cell in column E > go to next row and do the same.
Attached File : sample_data.xlsx
I have a problem to write a VBA code for the following task:
My worksheet has a column that has a unique code in column C (Ex: HR1) which is not repeated throughout column C, and I need to insert the amount of money spent on this specific code.
I would run a userform that has two text boxes, one that I shall enter the unique code and the other text box shall be the amount of money spent. I would like to know of a vba code that can search the unique code of the value entered in the first text box of the userform, then to enter the amount of the second text box into column D in relation to the respective row which the unique code is located.
I would like that the amount entered in column D regarding the unique code could be added automatically by entering any new entries in relation to the unique code selected, if this is possible. If not possible, to go inserting any new date into the next column regarding the same row of the unique code.
Ex:
A
B
C
D
[Code].....