In the attached document i need to work out the following solution for a formula but im baffeled! In the completed column i need to show 'ongoing' if there are any 0's in the 101 - 104 columns and 'completed' otherwise.
View 3 RepliesWhat I'm trying to do is, create a formula that works like vlookup, except I want to return the value of the cell that is a specified number of cells below the looked-up data in a single column array. So, let's say cell A1 reads "Bacon". In B1, I'd like to put a formula which searches the array for the cell that contains "Bacon" and then returns the value of the cell underneath, let's say the value is "Eggs". Then in C1, I'd like to put another formula which also looks up "Bacon" in the array, then returns the value of the cell that is down two cells from the cell that contains "Bacon", let's say the value is "Milk". And then in D1, a formula that returns the value 3 cells below "Bacon", and so on. All the values here will be text, and not sorted in any specific order. I'll be using Excel 2010.
Please take a look at the attached sheet if it clarifies things.
I have a number of dates (columns) and under each date there is the demand value (Rows). Also, i have a column that has the current inventory. what im trying to do is to keep on adding the demand in one row (i.e multiple dates) until the sum just exceeds the inventory. After that, i would like to return sum the date at which we stopped adding. the point of this excercise is to see at which month will our inventory deplete according to the demand. Below is an example solved by hand.
Demand
Part #
month 1
month 2
[Code].....
Basically, 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 have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
I am looking for a formula that will enable me to determine which one out of five thresholds a percentage falls within and the retuns a specific value for the corresponding threshold. Here is an example of the data (only showing three thresholds):
_____A________B_________C____
1 ___0.0%_____50%_____$5.00
2 ___51%______75%_____$15.00
3 ___76%_____100% ____$25.00
Where the values in A1 and B1 are the low and high ends of one range. I would like the formula to determine which range the value falls within and return the value in collumn C (C1 for the A1:B1 range).
I can do this using multiple 'if' statements, but am looking for a much more streamlined way to determine the proper value in column C.
I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
For Example:
Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie
[code].....
ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.
A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'
I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?
I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.
I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.
Multiple commodities and multiple engineers should be separated by definite structure.
find worksheet with input and output (manual) as expected.
How would I pull only a specific word from a text cell? If you see my example, in one column I have various job titles. If A2 contains "Manager", then in B2 return "Manager. If A1 contains "Coordinator", return "Coordinator. Etc.
Excel Forum.jpg
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
I've attached the example spreadsheet.
I have 3 columns with multiple variable rows.
The 4th column is the specific result for those 3 variables.
I need a formula/s that will give me the correct 4th column # based on the previous 3 columns what would be input using dropdown lists I believe.
So for instance the final end result would be an HTML web page with 3 drop down boxes representing the first 3 columns and then based on what is selected in those 3 dropdowns the correct 4th column # would be shown.
if I wan to get the value of specific column, how should I use it by vlookup?
The formula for vlookup is like is =vlookup(lookup value,table array, col_index,range lookup)
If the col index range, can i specific any column, example of I lookupvalue from A, then get value from CH?
I have 3 named range columns to query and a fourth from which I wish to return a value
Column 1 is called DateOE, column 2 is called NameOE and column 3 is called RunOE. The column from which I require the value is named ConcOE.
I have the following formula:
=IF(AND(DateOE=28,NameOE="Wayne",RunOE=1),ConcOE,"No Data")
My logic dictates that the formula should return whatever was run by Wayne on run number 1 on the 28th day from the values within ConcOE or return the value No Data.
The run numbers are unique, which is the identification key.
Every time I try it out, I have a #Value returned and if I convert to an array, the value no data is returned, despite the fact that I know what value should be retuned.
I am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.
For example:
The keyword is blue:
birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today
blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my
see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in
I have a list of names, and each name belongs to a particular group
I want the group number to auto populate in row 2, so when Andrew adds his name on the bottom of the list, row 2 returns a value of 1, Barry, group 2 and so on.
The list of names and groups will remain the same over time, but the list will be repeated each time that the individual inputs his name.
All information is in one sheet as below
Name
Group
Andrew
1
Barry
2
[Code] ........
I am trying to return a number with a specific format. I need Excel to look in a particular column and when it finds the entry in the correct format to return it in a new column. I want it to repeatedly return the number to the new column until it finds another number of the same format in the original column.
View 2 Replies View RelatedI 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 a workbook with several sheets of data - it's basically names with a series of assessment data. Each name and selection of data is in its own row.
I want to build an additional sheet with a search function/drop down box that wil allow any name to be searched for/selected and return that specific name/all/specific cells in that row. I believe this could be done either using VLOOKUP or a Macro but I am only a beginner with either of these things.
I have my tracking data sheet in my master file.
I wish to create a macro code to only retrieve the data on the file last modified date with the specific date.
For example like this :
I wish when I click on the List button, it will pull out the file that is from 28/5/2014 till 30/5/2014 which I have input in B6:B7
I have my code like this but it will pull out all the file in the folder:
Code:
Dim iRow
Sub list()
iRow = 11
Call ListMyFiles(Range("C4"), Range("C5"))
End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
[Code] ........
I'm using excel 2003 and I have have a dynamic string of data separated by 19 commas ",". I think 19 (the # of commas) is one of the few fix numbers...
What I'd like to do is from Right2Left return the 5 characters immediately to the right of (before) the 11th "," comma (i.e. 22.59 for the 1st string on Excel Cell A2) OR from the Left2Right return the 5 characters immediately after the 9th comma "," comma, which is also 22.59
Example of some of the strings I've been trying to work with...the list is much longer...but for example sake I've limited to 4...
Sheet1
BCDEFGHIJ2Product10/08/200917/08/200924/08/200931/08/200907/09/200914/09/200921/09/200928/09/20093A228157989393994B1784371107922385C4483398261701298
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
And I am after a formula that will return the total sum of values between two specific dates.
So if my results table looks like the one below, the values the formula would return are shown in Cells E9:E11.
Sheet1
BCDE8ProductStart DateEnd DateTotal9A24/08/200914/09/200934110B10/08/200907/09/200918511C31/08/200921/09/2009225
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
View 8 Replies View RelatedI have a list:
Item Code - Description - Quantity - Date
02099 - Blue Widgets - 500 - Jul 1
02099 - Blue Widgets - 200 - Jul 2
02099 - Blue Widgets - 300 - Jul 5
02099 - Blue Widgets - 500 - Jul 9
What formula can I use that returns the date (Jul 5) once the Widgets reaches 1,000 pcs?
I've got a database that is sorted by date...
I need a formula that will look for 2 criteria and once it finds those 2 matching criteria, I need it to return the information on that line that's in column 6, let's say.
so in one column I have the date, the next column I have the sales persons name, 4 columns over I have their order number.
In cell a1 of worksheet 1, I have a drop down ready that has all my sales persons listed. In cell a2 I will manually enter the date that I need to reference.
I need the formula to then look for cell a1 and a2 in worksheet 2 where I have my spreadsheet with the info I mentioned above, match that criterium in worksheet 2, and return the info in column 6.
I need to check one column to see if it is less than another one
If it is I need to have the IF function return specific criteria for true and then for false but then I also need it to rely on the results of another question from another column if the result of the first query is less than the figure in another column again I need to return specific criteria for tur and then for false
An AND function requires both to be true but when one isnt what do I write?
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select