I'm not sure if this is something that can be done by macro, I have a database setup using a spreadsheet in Excel. The first column is a list of customer names, that can be filtered by customer name. If I filter the column, using for example the name Jones, is there a way to add up all the units of the product that they have purchased?
View 9 RepliesI am trying to set up a costings sheet for my other half. So far I have worked out his costs for this basics i.e. van etc. man power, days, mileage. He now wants to be able to enter his miles to a job, man power, days etc, Then to show total invoice cost and show profit i.e. if miles need to be changed.
View 3 Replies View RelatedI have set up a spreadsheet but need to add a tick box, I just need to tick it once the item has been completed.
View 6 Replies View RelatedI have a small spreadsheet setup for my job where i have to input the time on arrival in L3 which is linked to a NOW() formula. this is it (=IF(L3="",NOW()
View 9 Replies View RelatedI've set-up a basic stock control spreadsheet. Part of this requires updating prices of products. My approach was to use 3 columns A,B & C. C would show the current unit price of a product. Where there is a change in price this would be entered in A which would then result in a new average unit price to be calculated automatically in C. When ever this change occurs i need the then 'old' price which was in C to be shown in B aswell.
I've tried using various averaging formulaes but i'm thinking they were probably to basic or just plain wrong
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?
View 9 Replies View RelatedI run a bowling leagues which as 7 divisions and 10 teams per division, some of the clubs have up to 4 teams entered. I keep a spreadsheet for each division which keeps records of each teams performances as well as individual. All the clubs have to register their players for which I keep a data base for each club, the clubs having 4 teams register quite a number of players. My problem is I have to manually check the data base against players being entered on score cards and then on to the spreadsheet. Down columns B & C on the spreadsheet I have the Forename & Surname, when I enter a name in the cells I would like a formula to check against the clubs data base and return the name or false
Players Reg.xls
2014 stats - Copy.xls
create an excel spreadsheet, where i can enter all my products name, unit price etc. so when i am doing an invoice for the customer, if i just type the product name price will show up automatically.
View 4 Replies View RelatedI am creating a spreadsheet utilizing the National Vulnerability Database (NVD) from NIST.
I am successfully able to import the xml files and have the xsd mapped fine.
My problem is within each xml files for each records(1000s of records) there are sub pieces to certain record parts. I.e. software versions (that will be different per piece of software) however they will always fall under prod vendor
"
"
Excel creates a new row for each of these.
How can I make it only create the 1 row and comma seperate those?
I would like to know how to migrate an Access database to a spreadsheet, I do not mean Exporting.
More specifically, the migration is conditional. The spreadsheet only needs a few fields from specific tables in the database. It is basically a data dump, however, the magnitude of the amount of information that I will be handling will be far too time consuming and not within the scope of the budget to do this all by-- Exporting-->Copy-->Paste method.
Also, changes often need to be made "The night before..." so the macro will need to be able to ran possibly a number of times to update the additional data or changes in data.
In my research I have found a few snipets of VBA code from the MSDN website as well as some other websites, however none of it seems to be consistent with one another and ultimately results in it not working.
Is this possible?
I used to use Excel many many years ago for all my basic business databases and pivot table reports (so was at a basic competency level) and want to use it again but it has been quite a while and I can't remember how to do the following.....
I want to build a database of sales and purchase info in say 'sheet 1' - let's say 30 different headed columns and then many lines of different information under each column.
In 'sheet 2' I want to create an invoice template of my own design (I can do easily) for printing.
This template will need to extract say 10 bits of information from a particular row and then put them in a certain place on this sheet.
How can I type in one piece of information in a cell on sheet 2 (say the invoice or line number of the database) and that will then trigger all the other needed information from that particular line in sheet one onto this template?
I know it can be done but for life of me can not remember the formulas to use for these other cells in sheet 2 based on the info put in that one 'trigger' cell
I have created a spreadsheet containing about 30+ macros and then copied it with a new title eg, sheet2013.xlsm (original) and sheet2014.xlsm. My problem is that every time I open the 2014 sheet it re-connects to the macros in the 2013 sheet (if this makes any sense). Is there a way to lock macros to a spreadsheet, ie open both sheets and use the macros within those sheets?
View 1 Replies View RelatedI would like to be able to password protect each sheet in a workbook and still be able to use the macros/form control (button). Below is the code that I'm using and the code that I've attempted to add to it to protect it.
Current Code:
[Code] ....
Code added for protection but not working:
[Code] ....
I've added this code to both the beginning and the end of my code; however, I'm getting a "400" error when I try to use the form control (button) when the sheet is protected. Is there something that I'm doing incorrectly?
I have 3 macros, 2 of them puts formulas in 2 separate cells and the other to turn off file save, file save as and the Save button. Everything was working fine until I password protected the project, now the macros quit loading. I can still run them manually and they work fine, they just don't run when the spreadsheet is loaded. (What I wanted to do was prevent users from seeing the formulas by saving the spreadsheet after the formula was put in the cell and also by looking at the macro). I've unpassword protected the project and took out the macro to prevent the Saves. What do I need to look at to get the macros to run when the spreadsheet is loaded?
View 4 Replies View RelatedI 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.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedBasically, i've got 90 players on a ladder. When one beats the other they replace them in their position and the beaten player moves down one.
Has anyone got any idea how I can set it up so that I have got a box come up with players names in a drop down box. I can then select the player on the left who has won, and the player on the right who has lost, enter it and the ladder to automatically update.
I have an Excel Workbook that is linked to Word.docm files and I want to put them on a CD, But I need for it to have a setup/uninstall format attached. Or maybe you might have a better method. The reasons for the setup/uninstall is for the unknowing person, with limited computer skills. This way from the autorun to setup/uninstall it will already give a destination to install file and add an icon on desktop. More user friendly.
View 9 Replies View RelatedIf I have a database of 1000 records that are logged and counted as to which of these records are successful and unsuccessful sales,
can excel be programmed to give an accurate forecast of what will be the number/ percentage of successful sales records over the next 100, 50, 30 and 10 new record entry ranges, using the database history?, then which of the new records within each selected range are most likely to be successful sales using the database history?
If so, what components or data references are needed in a worksheet to meet these requirements to allow for creating an accurate forecasting application within excel?
This might seem a strange type of Excel question.
Some "Clever" person decided to create a site plan using MS Excel. It has been done quite well tbh. The problem with Excel is that I am unable to set a custom page size as this plan sits nicely on a paper size I made ealier through using Word. Guess what there is a custom page size selection on Word but not Excel. Is there a walkaround for this ?
The only other way I can do this, is to print off 2 a4 landcape print out but the printer goes to the next page and leaves a nasty seperation.
The copy of Excel i have is 2003 sp3
When I protect the entire sheet, I can click in a cell and see what's in it, when I protect just a portion of the sheet, I can only click in the unprotected cells. Is this an Excel thing, or am I doing something wrong when protecting? I would like to see what the formula is in the cell when protected, but I can't click on the cell, what's the deal?
View 8 Replies View RelatedWe have 100+ clients. Each client has inventory in our warehouse. Every day we write down how many items went out to our client on a piece of paper and subtract that number to get a balance inventory. When we run low, we order more on a new FO number. What is the best approach for tracking this? I was thinking one way is to put our clients in a drop box at the top of the page and then the data will appear at the bottom, however I can't get that to work. I am trying filters to see, but I am unable to subtract the balance automatically from the last FO# and from the correct area.
Is there a way to choose a client and product at the top of the page and have the tracking below?
I'm trying to Pearson Ranks correlation table. This is the formula I'm
using:
=PEARSON(Ranks!$B$3:$B$230,Ranks!B$3:B$230)
This is placed in cell B3. When I copy the formula over to DT3 the formula
correctly adjusts the B in the second part of the formula all the way to DT
and still references the values in the B column.
But I want to now copy this down to C4, D5, E6 and then over to DT for each
so that it correctly makes the correlation table.
I set up a spreadsheet for use by my colleagues, on an Acer 17 inch portable. My colleagues all use HP or Dell with 15 inch monitors (and different graphics cards to mine). They all complained that when they opened my sheet, the page breaks were all in the wrong place. When they fixed the breaks on their machines and I opened those file, the breaks were now in the wrong place on mine, but were all right on theirs.
I have now "upgraded" to a Toshiba 17 inch laptop, and find that my original spreadsheet done on the Acer 17 inch shows page breaks in the wrong place on my new machine.
Is this something to do with display adaptors? If so, does anyone know what I can do to standardise this across the board? (My boss uses a 20 inch screen, and the sheet for him is even more wildly out).
This part of my macro can be improved as it runs through over 100 sheets and takes a while?
Sheets.Select
For Each sh In Worksheets
With sh.PageSetup
.Zoom = False
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = "&P of &N"
.RightFooter = "&D"
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.354330708661417)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = 1
.BottomMargin = 1
.Orientation = xlPortrait
End With
Next sh
I visited all of the sites listed in the various posts & downloaded the XLM function help file.
My question is which syntax would I use? I'm wanting to add this to an existing macro (one that currently takes FOREVER). I think syntax 1 for worksheets & macro sheets, but I'm adding to visual basic, so maybe syntax 3 for vb modules?
Here's my existing code, if anyone wants to help me change this to XLM4pageSetUp ..
how to setup calender object in VBA application
View 4 Replies View RelatedIs it possible to protect against users changing the page setup?
View 2 Replies View Related