i have several sheets with data and in the last sheet I want to get the latest 3 sales of a product, calculating the avarege discount of it and the sum of the units sols in thos latest 3 sales (product may have been sold les than 3 times)
Sheet 1 have the data of the articles
Code Art descripcion price
12 TV 29" 500
13 TV 21" 300
14 TV 20" 250
Sheet 2 has the sales during a period of 2008 orderd by date
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 am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database A B C D E F G 10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
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?
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've been browsing through postings for a while now trying to figure out a solution to my excel formula problem. While there is a lot of useful info, I have not found anything that will get me where I need to be. I am hoping that someone here will be able to me out. My project is this.
-A workbook with about 20 sheets.
-Sheet one will be a master list of serial numbers with blank fields for dates, account numbers etc.
-Sheets 2-20 will be used to assign those serial numbers to individuals.
-On each individual's sheet there are fields for dates, account numbers etc. next to the serial number.
-I need the master sheet to look through the individual sheets and insert the date, account numbers etc. into the master sheet.
I can get the IF function to work with one sheet but cant figure out how to do this across multiple.
I want to set up a database which contains customer comments and phone numbers, what I want to do is every 30 days or so, have the cells highlighted so that they remind me to call them.
uses CSV files for importing product data, problem is both have it formatted differently. What i ideally need is a master file that i can change whenever a price needs changing or product adding, this feeds down into 2 csv files that are formatted for the relevent softwares and then i can use a macro to upload these automaticly at end of day. ive tried using just excel but had no luck, i tried using access and custom reports but keeping getting errors as page size is 35 fields long and doesnt want to export as a csv,
Using an existing database with nonfixed boundaries I am creating a new one with uniform boundaries. The simple spreadsheet I will use in this comment isn't what the database actually looks like but represents what I am trying to achieve.
Here I have the old data, organised into sections that have the same assigned number. (Note: the assigned number may be the same as the previous cell). I wish to change it into this structure:
StartFinish# 020 2040 4060 6080 80100
It will be organised by the start and finish instead of by the assigned number. Instead I would like the number to reflect the lowest assigned number that occupies a part of that area.Is it possible to create a forumla which would achieve this?
I have a big database that has different name in column A(something like Y1, Y2, Y3, Y4, Y5, Y6, Y7, Y8...). What i'm trying to do is to group them! In group A i have Y1,Y7, in group B i have Y2,Y3, in group C i have Y4, Y6 and etc! I think the solution is something like index...but i never worked with that in VBA! So each name of group A should copy on a new sheet, the next rows should come group B and etc! Is there a way to do that in VBA?
I'm generally trying to figure out in my head most of the pluses and minuses for using Excel Worksheets as a database. In My case it means adding data from a UserForm to Excel cells and upon these data to analyze the info from the worksheets to other worksheets like charts, tables etc. Here's my view:
Pluses: 1. VBA and Excel integrate well as a whole, easy to use, well know interface 2. There're many available options to manipulate with the database once it's arranged properly in the excel worksheets 3. Don't need to give extra money software like Microsoft SQL etc.
Minuses: 1. Slower operating because it takes some more memory and processing time to reference the data in the cells. Here I mean operations between the UserForm(s) and the Worksheet(s) 2. You can have DB not bigger than 256*65000 3. XLS files are generally much bigger as a size -> slower work as a whole 4. Maybe (i'm not sure here) there're less tools and options than a real SQL software
What options I have: 1. Use some SQL DB which is free 2. Use Text Files to write and read from
I am trying to find functions that allow me to categorise game attendance by team and day. EG: Out of the list, I would like to summarise the min, average and max attendance for the Broncos on Fri, Sat, Sun and Mon games (and for each team)....
in summarizing my data set. specifically, I have names of organizations listed in once column and in one of the others I have status of organization (open, closed), number of transactions and volume of transaction by months for each organization. In a summary sheet I need a formula that would look up for a specific organization in the long data base and match it with its status and count the number of "opened"(or "closed") organizations matching that specific name as well as sum the total volume of transactions for that specific organization. If I stated my request unclearly i can explain it better or attach a excel sheet.
I am working on a database for a client. The database has a few worksheets with an overview (contents) worksheet. What I am trying to do is create a hyperlink from the contents page to the corresponding worksheet. I can do this no problem...my problem is that if I add more data to the worksheet the hyperlink is lost. I am linking the sheets via cells so if a cell is moved down to add a new cell the hyperlink is no longer valid. Is there a rule I can create to keep the hyperlink and not have to adjust it every time new data is added.
