I need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.
View 2 RepliesI need a formula that looks at a Cell and if it contains any text at all it will return a numerical value into a seperate cell. I have done this before but for some annoying reason i cant remember how i did it.
View 4 Replies View RelatedHi all, first post here.
I've been asked to do something in excel i can't seem to get my head around.
My client provides me with data in the form
1c, 1b, 1a, 2c, 2b, 2a, 3c etc... where 1a is the lowest.
I need to find a way of working out the difference between these, e.g.
edit: 2a - 1c = 5
I've been working on it and have made some headway by splitting these with MID function and then using CODE to try and work out a unique ID for each, but i can't help feeling there's an easier way!
I have a single column of numerical values that may repeat many times within
the column.
I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered – so only take into account Visible Filtered Cells.
The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.
The Formula should Return two numeric values: a Start Value and an End Value
– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.
The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies View RelatedI have a bunch of domain names (including subdomains) in column B
In column A i need just the raw domain name...
example
a1 [domain.com]
b1 [domain.com]
a2 [domain.com]
b2 [mail.domain.com]
a3 [domain.com]
b3 [subdomain.domain.com]
It's a spreadsheet of about 300 rows, each row corresponding to a certain department. each row is filled with various data, both text and numerical, including various different scores and averages. the columns include daily scores and weekly averages. I want to be able to create a new column that will calculate the time (in days or weeks) since the last score/average was entered.
1. a formula that will return the address of the last numerical entry in a given row. (I've got a MATCH formula that will do this, although it doesn't let me specify different cells or ranges, only the entire row)
2. whichever cell is returned will give the date instead of the value. (for example, if the last value entered is in $F$63, and Column F is headed 23/11/09, then the formula will give the date)
3. then finally, calculate the time between that date and the present time
I've tried various different things, both in normal Excel and the VBA editor, and I'm beginning to think maybe this isn't even possible.
What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.
I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.
I am looking for a Formula that can process the following:
The Sum Target Value is variable e.g.; 147
I have two columns of numerical values: Column “A” and Column “B.”
Column “A” Houses the Numerical Labels that I wish to have Returned when the
Sum Target Value is processed /reached - Subtract Sum Target Value as noted
below.
To Subtract Sum Target Value:
Start from LAST non-zero numerical value in Column “B” and Subtract one Cell
value at a time (or Sum up the Column) until the Sum Target Value or nearest
possible Sum BELOW, the Sum Target Value is reached. In this instance, it is
147. I wish to Sum the values in Column “B” to 147 per the above. The
summed values can be below BUT NOT over the Sum Target Value.
Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”)
and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target
Value. The Result – Numerical Label should come from Column “A.”
Col “A” Col “B”
2003
2051
2105
21511
22010
22515
23016
23510
24011
24529
25020
Reaching the Sum Target Value of 147 in Column “B” would go up to value 26,
Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but
including the value of 31 above it, would exceed the Sum Target Value of 147.
The required Result is returned from Column “A” Label 290 which is ONE Cell
to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column
“B”).
I have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively.
What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()?
Is it as simple as:
Function DocumentType()
If(...........)
End Function
I would like to use a macro to find a number in a worksheet, other than the worksheet i'm using the macro in. The number - in this case a material code located in Column A - must be entered by the user in an inputbox, and then return (copy&paste?) the value of another cell (the price of the material - in Column D) in the same row.
I recorded a macro envoking the find function, but then it only finds the number which I used during the recording. How do I change the code so that the user can input any number, or what code must I write?
I am trying to log a specific portion of code from a webpage. The line of code looks like this:
View Details
I need to extract the userid protion, the part between "=" and "'target...." and then
Working with project information which is about 1,000 project. I have two worksheets one has the start date/finish dates for each phase of a project -Define, Measure, I, Analyze, Control. Depending on the date I need it to be read and insert a value in my summary worksheet. The summary sheet contains the 12 months. I need the formula to read two columns start/finish, if the month is the same as the header in the second report return a letter of D, M, I, A, C if not leave blank.
View 9 Replies View Relatedfind the first numerical in a text string that is immediately followed by an x
so that i can extract the packing size from packing description workbook attached
DESCRIPTION
PACKING SIZE
SUNFLOWER OIL 12X1LT
12X1LT
[Code].....
I am drawing a complete blank of this one. I know it can be done but I can't seem to remember how I did this in the past.
I have a sheet that has cells A1 at MB, A2 as ML A3 as MD
Then in cells B1 I have MB,TL then in B2 I have ML and then B3 is Blank this time.
Then in cells C1 I have Jim, C2 has Jack, and C3 has Fred.
What I want to do is have a formula that is in cell D1 that looks at the value in A1 and finds that value in Column B and if it is found return the value in the same row in Column C. In the example it would show Jim since MB is found in cell B1.
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies View RelatedI have attached a file to make this easier. Basically I would like to search "Points Scored" (B2:W9) for the highest score (which I already have done) and when it is found return the team's name that is associated with it. Then do this for the second, third, etc. as it can be seen in the "Main" sheet. I'm thinking an index/find approach, but I just keep getting "N/A".
View 7 Replies View RelatedI've got a spreadsheet with various amounts in cells A3:L5. I want to find the last non-blank entry in each row but only where the entry is in a column headed "Guaranteed PRB" (found in row 1). Then I want it to return the column header title found in row 2, which is a date.
I've attached a sample of the spreadsheet with the expected answer in column M.
I've got as far as formula:
=LOOKUP(2,1/(a3:l3<>""),$a$2:$l$2)
how to also make it look at row 1's headers too.
I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:
=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)
but all I get is hashN/A?
I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
I have a chart:
ABCDE126143199167233153143310351018220110246817085151581166230162692149206173711417214213281273781662029132178200255
I need a formula to find the maximum value in C1:E9 and return the corresponding number in column A. In this instance, the answer would be 12. I've been trying to make it work with a combination of Offset and Max formulas, but since the maximum could be in any one of three columns, I don't know how to make it work.
I need to write a conditional formula that will look up in a 2nd worksheet the last coloumn with data (note that there will be some coloumns in the row that will have data and some that will be blank, I need the last column for that specific row to be the return result)
View 9 Replies View RelatedI am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer.
What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that.
So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
I am trying to create a run balance sheet (see attached sheet). Column C has a list of job numbers, with column D showing the reqd quantities. Once the job is run, the qty is entered in column F and either a balance or the word complete returns in column H. My problem is, is that, when a job with a balance attached to it is re-run, that balance should be returned in Column D.
Ie 574361 has a total of 707 of which 320.4 was run, leaving a balance of 386.6.
When I type in 574361 again, I need column D (in the cell next to the job number)to automatically locate the last reported balance and return its value.
I have tried VLOOKUP and INDEX, but cannot get the thing to return.
i tried using the lookup but it gave a different result. i want to search a value from sheet1 A to sheet2 A and copy the remarks from sheet2 B and paste it to sheet 1 B and if not found leave it blank.
here's the attach file i just trim it down.
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies View RelatedI have two excel files.
File #1 is a varying length and has 4 columns with ID #s in Column A.
File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B.
I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2.
I've tried using the substitute function but I could only replace one found ID at a time.
I have this table, which can be seen as a basic custom gantt chart: KLRWo.png
And I would like to fill the A column with start dates, based on the first filled cell of the range on the same row, and the header value of its respective column (row 1). It's easier to show my expected result than write it actually:
WiMZH.png