For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.
The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)
How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work
unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ).
The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me
The problems I need solving are:
I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this?
I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows: =VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0) but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following: =VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE) The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7 K,6,7,7,A,9
To reach the conclusion I need to build six such functions: =1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66)) =1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66)) =1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66)) =1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66)) =1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66)) =1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.
how would i go about eliminating the duplicate strings of text quickly?
In Column B I have some measurements. These are measurements. I chart them in a line graph. I also have my max and min over in some other cells. These aren't the min and max of column, but these are what I have set the min and max at. To make this clearer, my max is 27, but the data in Column B could be anything....20, 28, 35, 15, etc.
I want to plot my data in Column B just as I am currently doing. However I also want to plot the min and max on that same line graph and show them has horizontal lines going across. That way, if one of my numbers is above the max (or below the min), you will be able to see it on the line graph.
Here's what my data looks like. The Viscosity (column b) is what I'm charting on the line graph and you can see the min and max data that I want to plot as well.
I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.
I need a macro that add's after each 9 lines 2 new rows. The data I've it's not the same every month, one month I can have 27 lines the next one I can have 90 lines for exemple.
I need also to calculate in one of those added rows this "=B8+B4-B6". This calculation is to be apllied in each block of nines lines.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries Mar Apr May Jun
Loans to banks Mar Apr May Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I like to create a float chart in excel. for this purpose I need to have a candle chart and add lines into the same chart. I can create a candle chart in excel. But as soon I add lines to it (additional data rows) the candles are distorted. The principle I'm interested in applies as well to: how do I add moving averages into an excel candlestick chart? As soon as I add the data for the MAs, the candles are distorted.
In short: how do I combine candles and lines in one chart?
I have an excel document that looks all nice and pretty except for the little white grids all over it with unused cells. Is there anyway to hide the lines? i dont care that the cells are there just the lines make the form look kinda crappy.
I have created a quick flowchart in Excel 2007 using the shapes and elbow/line connectors. All looks ok when on screen but when I move to the print preview, the elbow connectors used to loop back (horizontal, vertical and horizontal in opposite direction) in the process jump out of position.
If I print, it prints as per the print-preview.
Have alook at the screengrab showing regular view and print-preview.
Is there any way to fix the position of these lines?
Supplementary question: is there a straighforward way to creat a line break or crosssing?
I'm having an excel sheet with addresses. I need to copy some lines of the first sheet to the other sheet. Between the lines I want to copy there are 7 lines which shouldn't be copied.
I created the following macro which does the job: ...
How do I turn them off? I go to print preview, then leave, and I have ugly lines all over the place showing where the end of the page is. My entire team can't figure it out. where I can turn this option on/off?
I have these light blue lines showing up on my sheet. I have "not boarders" selected and they do not print - but I can see them on the screen. Does anyone know how I can get rid of them?
I need to turn millions (I'm guessing around 4-5 million, though I'm not completely sure since I don't have a program that can actually open the file completely since it's so large) of lines of text which are formatted like this:
05/15/09 09:45:01 AM 16600 1
into lines which are formatted like this:
20090515 094501;16600;1
I could do it by hand, but since there are literally millions of lines like this, it would probably take me about a year to do. So my questions are:
1. Is there a way to do this relatively quickly in Excel?
2. When I open this file in Excel, the year and time tabs aren't filled in with the year and time, but simply with "#######", though if I click on the cell, the year or time is displayed up in the "fx" function bar thing. Is there a way to keep these cells from displaying as "#######"?
3. Right now when I open the file (it's a CSV file), it only opens a little over 1 million of the lines, and an alert pops up that says the entire file was not opened. Is there any way to get the entire file to open? If not, how could I split the file into pieces that are small enough for Excel, and then how could I join those pieces back together?
1. Is where the user is putting in data. 2. Is a spreadsheet with the data choosen from the action of the user, but it is containing also the non-choosen data from the input spreadsheet. 3. IS how I want the data shown to me either automatic or thru a macro. So is there any code like IF or some Macro who would make this happen?
What I want at the end of the day is a list of products that the user have put in and nothing else, with the QTY and price and the sum of that.
In the attached spreadsheet my data consists of columns A through K. Essentially I want to line up the data so Column E matches column K. When no data exists in the other section, then a blank line should be inserted. The example I gave has the data formatted the way I want it. As you can see, line 11 is blank. Column L can be ignored as my manual way to handle this was to create an if statement that returned "Error" when something didn't match.
My spreadsheet has 29,000 lines in it. So doing this with my IF statement method would take enormous amounts of time.