This example is A1 to F4. There are two inputs; A10 and A11, and one output (formula) on A12. A10 will be the column I choose, and A11 is the row I choose...but it's chosen if the number is >= column A and < column B.
Example: I input A10 as 2, and A11 as 87, A12 will output 4. If I input A10 as 0, A11 as 85, A12 will output 6.
I kind of understand how to use VLOOKUP for this, but I am unable to check column A and B to choose the row.
I have a table populated with values for given dates. Some days there is no data. I would like a separate table over to the right to automatically populate with only the date and data.... (no spaces). I will attach an example problem to this Thread.
I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.
I've attached an example spreadsheet for reference. Basically, I need two formulas:
1. Formula to pull column header (Receiver name) based on Sender. This is a necessity. 2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).
My actual data file has 80+ senders and 100+ receivers.
I created a macro that opens a page as an excel sheet, grabs data from it, and inserts it into a cell.
My problem is that one of the cells has an abundance of data that I don't need.
The data that it puts into the cell looks like this: 0849940222,* Bill Myers ,* Thomas Nelson,*1997-10-02 The only thing I need is the first name that shows up.
The number at the beginning is always the same length, so I thought about using a trim function for that, but since names are not always the same length, how to do the other end. Maybe get the value for commas and delete everything before the first and after the second...I also don't know how to do that in VBA.
i want it to change the Header in E1 to the name of that price level and have the prices change according to that price level. The price level prices are currently being pulled from another tab through vlookups which lookups up the part numbers. My method only works with 1 price level right now and have no clue how to approach it with more then one price level. Also these part numbers will change positions and locations and are not permanent hence why i thought to use a vlookup.
Is there a way macro wise ( preferably through a formula ) that i can have Column E prices change according to the Price Level Entered.
I need the list in column "R" to become the list in column "T" but i need to have the list update everytime a number "New" number (not already in column "T")..so if i added "37" in column "R" it would show up in column "T".
I have a spreadsheet with info about students in a summer program. I need to pull out the students who have allergies along with what their allergy is and their emergency contact phone number. I would like this new info in a new spreadsheet.
Here is an example of the spreadsheet:
allergiesnamephoneallergy type nobob410.555.1234 nojim410.555.5843 yesmeg410.555.7458nuts nolex410.555.1159 yeswill410.555.5005fish
I'm copying and pasting data from a PDF and need to eliminate some unnecessary data. The original .pdf has 4 columns: Account, Dollar Amount, Name, Notes
When I copy this to an excel document, it copies the row from all 4 columns into column A. The two columns I need are Account and Dollar Amount. All account numbers are 8 digits so I was able to create a formula to weed that out with this: =left(A1,8)
The problem I'm running into is obtaining the dollar amount within the cell. For example:
A1 contains: 11112222 $1234.56 Sample, Name Sample Note
I pull the 11112222 with =left(A1,8) in column B but not sure how to pull dollar amount to column C.
Further, the dollar amount varies from $1.01 to $10,000+
In the little chart on the left, I have the customer name, when their story is due, and when it was completed. On the right, I want to show how many words per hour my journalist is averaging for each week. What I'd like to do in cells J2 through J5 is to have a formula that pulls out the total number of words written for all projects that occurred during that time frame. I can't seem to figure out the formula, though. I was thinking that I needed an array formula starting with ifferror and calling from the row functions, but I can't seem to get it to work out right.
Here's my sample sheet: sample data journalists.xlsx
i have this spreadsheet that has 2 tabs titled "master" and "unique". "master" has a list of ingredients (8 each), a number associated with it and a store number associated with it. for example (basil-136-r5). now, each store number has a different number associated to the ingredient. for example (basil-136-r5, basil-235-r6). on the "unique" tab/spreadsheet, im supposed to create a formula that calculates the number of times (ex: basil) is used and what number is associated with it to the store number. here is a pic:
There is probably a simple solution to my problem that I am just not seeing because I cannot believe I'm the only one trying to pull out the number of hours after summing a column of times when the total exceeds 24 hrs. I have attached a sample spreadsheet which has columns for start time, end time and time used (ie end - start time) Originally when I totalled the column of time used, I got 20:27 hrs instead of the correct 44:27 hrs. After doing some research I found out that I had to create a custom format of [h]:mm to get it show beyond 24 hrs. However, now that I can see the 44 hrs and 27 min as 44:27, I cannot seem to pull out the 44 hrs to use it in a calculation of multiplying total time against a charging rate ($/hr) to get a total cost in $. The sum using [h]:mm is in cell F68 and I was hoping to convert this [h]:mm into a number of hrs as a decimal (ie 44:27 to 44.45 hr) by =((HOUR(F68)+MINUTE(F68)/60)) but I get back to the 20 hrs of a h:mm format.
I was originally surprised that I couldn't sum a column of times and get the total time without creating a special format. But I am really surprised that it isn't intuitive to pull out the correct number of hours when it exceeds 24 hrs. I am sure that it's simple because it seems something that a lot of people would have going on in accounting type of spreadsheets. I am going to be doing a lot of summing times coming up and would appreciate suggestions. This forum helped me once before and I hope for the same results!
I have a worksheet that lists all employees (past and present) (Worksheet A). I have another worksheet that I am sent from our training department that lists monthly test scores (Worksheet B). The worksheet containing the test scores only contains current employees, and the employee list changes from month to month.
Is there any way to have Worksheet A match up employee John Q. Public with the row for John Q. Public's in Worksheet B? I would like to be able to import the test scores automatically regardless of which row the employee in on in Worksheet B.
What I am trying to do is pull data from one spread sheet into another. The Data spread sheet has 2 columns. Date and Price. What I need to do is enter a date in to row b2 (example 2/13/2013) Then Cells c2-v2 fill in with the data from the 20 days prior to 2/13/2013. Also note that I use only dates from weekdays.
I have created my main spreadsheet in a form format - one form per student in a row downwards (30 students) and then a row for each of 4 terms.
On a separate sheet I want to pull particular entries. I have done this for Term 1 and can continue and do Terms 2 - 4. However, I have to wonder if there is not an easier way. If not, then I'm good to continue - just takes a couple of hours to create the second sheet section for each term.
I am trying to create a simple ticketing system, just using the excel with no other PL.
I am trying to transfer the other in formation that I have create from 1 worksheet to another. If I input a particular number that I assigned I want that the other info along side with that number will automatically transfer to another worksheet.
I have a VBA code that pulls a value from a single cell on a separate tab. Instead of just pulling the value (ex. 3.44589348) is there a way to have it pull the value as a link to the other tab? It still would display the number but if you click on the cell it would display =Sheet2!A1 (for example).
Hi folks, I got great help just now on the first part of this project and am hoping to get lucky again. I attached a workbook. This is traffic count data. I need help pulling information from spreadsheet named HourTotals.
The data is in cells H2:AE366, it is 24 hours wide by 360 days long. I need to search that range for the highest number and in addition to the number get the corresponding date from the date_ column and the hour from the corresponding hr_X column and paste that information into another spreadsheet and repeat that process for say 500 values.
My goal is to create a pair of validation lists. The first will allow you to select what craft you are using (i.e. Alchemy). That will bring up the second list which will allow you select what recipe you are using (i.e. Black Ink). From here, I want the spreadsheet to automatically fill out what ingredients are in the recipe, the skill levels needed to complete the recipe at 100% success, and what the yield is each time successfully complete the craft.
The first part is easy, using the INDIRECT function to pull up a second list from the first. I have no idea how to make the other cells fill out, or even how to structure the data to make it accessible.
This is a simple question but I just cant get the right format. I have a userform which saves info entered into the form into a worksheet. I have one cell in anoth worksheet (sheet2) which I would like include in the info from the form.
I already have the column entered in the database I am now trying to figure out how to include this in the save.
I have searched extensively for an answer to this but can't find anything.
I am pulling data off the web and it puts data down the column in the following manner:
Data Number Number
Data2 Number Number
I need to extract each set of Data to another worksheet. The problem is that it will be anywhere from 1 row each to 10 rows each. In each instance, there is a blank between the two sets of Data. I've looked at ISBLANK, LOOKUP, SEARCH, etc. but nothing I've tried has worked out.
Is there a way to pull out numbers from a cell that has letters mixed in with it. I tryed using Right() and Left() with the LEN() function but sometimes I might have (examples: "TP3470" or "9310-AV") and all I want to get is ("3470" and "9310") I also may have (4500SSML). I cannot find anything anywhere on pulling out just numbers or text. Maybe a custom format?
Take a work book, In one spread sheet you have data containing information about a user in the cells going across. In Sheet 2 I need to pull certain information into a comment section belonging to that user. Is there a way to do this?