Any way to create a queue system in excel.
Here is what I would like to do:
Pull emails from outlook into a spreadsheet and click an add button that would add the From and Subject of the email to the bottom of the list with a timestamp showing the time the add button was pressed for that email. I would like the list to have the oldest timestamps stay at the top of the list, if possible.
In the following code, in select statement Type & Type1 & Type2 defines queue number. And now in sheet2 , there is a list of Queue names and corresponding queue numbers. So When the data gets copied from Access to Excel using the code below then i want the code that will check the queue number and replace it with queue name in Sheet1.
[Code] .....
create a unique scoring system on a set of given criteria that enables an even split in work for team members. so i.e. work comes in, and depending on the criteria of work its then passed to the relevant team member based on points. At the moment work is allocated A-Z which is working out unfair on team members as some are getting more/less work than others...a points system will enable a even split. so if a piece of work scores 30 points it goes to a junior member, if a piece of work scores 80 points it goes to a senior member and so on...my problem is how to link the points and criteria...Im guessing joint vlookups will be needed.
the only other way i can think a system is similar is like a fantasy football system...points based on criteria.
I am trying to create simple password protected system in excel. What I have already done is I have a worksheet, which is not visible that holds all user names, passwords and user roles, such as admin and user. Now my problem, I need to create a UserForm that will prompt user to input their details prior to using the workbook. I have no problems with creating the form itself but I cannot really create a code to handle the task. Also, there will be only one system admin, which means that only admin should be able to create users, so that when admin will sign in, he should be able to see the button on the home screen, something like "Create New User". I cannot post my workbook because of the Corporate Copyrights and privacy, but what I can do is post all named ranges that contain password, usernames etc.
View 2 Replies View RelatedI am trying to create a spreadsheet that will automatically create a ranking system after data is input manually.
If you look in the example, you'll see roughly 30 people will be ranked on various metrics, and on another sheet, I would like 1-30 to be displayed in each of the different metrics, thus creating a ranking.
I'm not sure how to go about doing this, and any help would be greatful. I've tried different excel tutorials and searched and searced the help features, but couldn't exactly find what i was looking for.
I have a worksheet that is going to be used my approx. 10 people at my company. I want to give access to certain menus to a few people and other menus to others. I have 2 custom menus. One is for each group, but I don't want to allow access to the other groups menu. Is there a way to create a way of logging in via username and password so that excel can display only the correct set of menus depending on who is using the sheet at the time? I think there is a way that excel can recognize the windows login and password for each person and maybe I can use that? If so, how do I then set it up to display only the correct menus to certain users?
View 9 Replies View RelatedI've been tasked with creating an updated booking system for a company which offers projects / services to the educational sector (UK Primary Schools.) I've done an alright job at augmenting their current Excel based system, but I would like to take it further so that the system is more all encompassing.
Currently, the booking form is filled in when a booking is made and the data from that is populated into their copy of the contract and our copy of the contract using simple "=" and to a certain extent, the text on the contracts is manipulated based on the booking form data using "IF" functions.
There is a basic macro which removes excess lines from the contracts if they aren't used, but that's about it. The difficulty we're having with the current system is that a lot of our projects are bespoke, happen over more than one date, and require a lot of manual manipulation in order to get them ready, which sort of defeats the object of having a 'clever' booking system.
I don't know very much about VB, but the sort of thing I would be looking to integrate is having drop down lists to select a 'project' which would load project-specific text into the contracts area. Also, instead of having to remove lines manually, I would like the data to be 'dropped in' creating rows as appropriate.
I'm trying to get one more feature added to this priority queue tool that a forum member created. Essentially, this attached spreadsheet is a dynamic task/priority list that will change on the fly. See the original thread here: [URL]
The only missing feature is the ability to delete an entry and have all other entries below it change up a rank.
The attached spreadsheet is the almost completed solution
Example:
Original
-----------
Item D - 1
Item B - 2
Item E - 3
Item A - 4
Item C - 5
Remove item B
-----------
Item D - 1
Item E - 2
Item A - 3
Item C - 4
I have a simple Excel file composed of three tabs:
-EDIT
-QUEUE
-RESOLVED
On each tab, line 1 has the same headers: Status, Name, Surname, Issue, Details, Last Update
In the EDIT tab, line 2 is dedicated to editing the relevant information under the headers, and once done is validated with a "Done Editing" button: This button would cut line 2, and place it in line 2 of the QUEUE tab, moving down all the others in the list
The QUEUE tab lists all the issues keyed into line 2 of the EDIT tab. An "Edit Selected" button would cut the selected line (or the line in which a cell is selected), and copy it to line 2 of the EDIT tab for further editing, after which it would be placed on top of the queue once again as above.
The RESOLVED tab lists all of the issues marked "Resolved" on the Status column.
What I can't figure out are the macros for the two buttons:
- "Done editing" in EDIT tab adds today's date in the "Last Update" column, then cuts line 2, and pastes it in line 2 of the QUEUE tab pushing down all the lines in the list, and keeping any existing conditional formatting if possible (colours in the "Last Update" column, for ex.). If the status is set to "Resolved" before clicking the button, it does the same, but pastes it to line 2 of the RESOLVED tab instead of QUEUE.
- "Edit selected" in the QUEUE tab just cuts the selected line from the QUEUE tab and pastes it in line 2 of the EDIT tab.
I am making an Excel calculation system where I have two factors (weight and opening length), which is essential for which lift there is possible to use.
There are 8 models each of which may have some limitations in terms of weight, and the opening length:
ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 130 350
HCV 230 600
HCV 330 800
HCV 430 1000
HCV 550 350
HCV 650 600
HCV 750 800
HCV 850 1000
What I am looking for is a formula, that when you enter numbers in two cells "Weight" and "Opening distance" of what their platform is to meet, then all the models that have the potential to accomplish this task pops up.
Example: A client writing a weight of = 40 kg and opening length of = 450 mm. The formula should then show which model that meets the requirements.
In this case: Models that meet the requirements for the lift.
ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 650 600
HCV 750 800
HCV 850 1000
There are many software solutions for clinical information systems, but they all cost a lot. It occurred to me that a bespoke system could be build using excel.
View 1 Replies View RelatedI need to get my system's time (in hh:mm format) in an excel cell and this should get updated as and when sytem time gets updated.
I have a workbook that the accountants use each month to supply our clients with thier monthly financials. One of the problems is rounding between Excel and MRI (Host System). I am using an array formula when totaling the various columns and this takes care of most rounding issues.
What I would like to do is have a macro that can be assigned to a command button that the account can click on when there is a rounding issue. The macro would ask the accountant which cell that needs to be adjusted (the cells are protected) and then another box would pop up asking for the amount (i.e. .01, -.01) then the macro would add this to the formula in that particular cell.
setting up a stock ordering system in Excel. I have been sent a spreadsheet with about 400 items I've ordered before and about 15 columns of descriptions which include the name, category of item, manufacturer, cost, number etc.
I would like to set up a simple Excel spreadsheet which staff can use when they want to order some stock, which will automatically fill in the adjacent cells with the product category and unit cost, once the name has been typed into the first cell. Once I add the SUM formula I can therefore have an immediate total of how much each staff member is spending on stock that week, without having to look at the invoice I get back from the supplier.
I know Excel has a predictive text style feature (autocomplete?) but I don't know how to get it to automatically fill several cells in a row with data that is linked to the first cell.
i am trying to make a double entry bookeeping system in excel just like VT cashbook and VT transaction
View 3 Replies View RelatedI am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.
View 1 Replies View RelatedIs it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
Looking to create a system to measure KPI (key performance indicators). I need to include a weekly target and then measure performance for that week against the target. I want to create a form to make data entry simple, how best it would be to implement using excel. Would I have 2 tables, one for targets and one for performance and then use look ups, [URL]
View 3 Replies View RelatedI am creating a login system for two users on a excel worksheet. I have the two users on a drop-down list, Maria and Andrew, which then requires a password entry. This is linked onto a data validation to make sure that the password matches the data table.
After, I then click on a macro, which, when successful, takes me from sheet 1 to sheet 2.
My problem is that I need to have it so that Maria is taken from Sheet 1 to Sheet 2, and Andrew from Sheet 1 to Sheet 3.
I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:
1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works)
2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works)
3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)
The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."
I'm guessing there is a setting somewhere in Excel that has changed as part of an update.
I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.
For what it's worth, here is the macro code.
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Run "PullInSheet1"
Dim oldbook As String
[Code] .......
In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.
Code:
Sub PullInSheet1()
Dim AreaAddress As String
'''''' Sheet11.UsedRange.Clear
Dim ClRange As String
ClRange = "= 'L:ADMINEMPLOY SERVICES" _
[Code] ........
There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...
1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.
2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.
The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.
I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem).
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
within excel for a big spreadsheet they developed?
can you suggest a way of doing this or an article to read?
could users press F1 and let my own custom help guide appear?
Just striving to have this kind of "picture arrangement's macro" to work, macro that whenever you choose a certain cell and operate it on hotkey it'll:
1. Create a new folder named "PicturesAuto" beside the excel file (wherever the excel file located).
2. Inside folder "picturesAuto it'll create a new subfolder named by the same name of the sheet related to that cell you operated the macro upon. (let's say this cell placed on sheet named: "happysheet"- that'll be the name of the subfolder created.)
3. Inside the subfolder "happysheet" it'll create a new subfolder named by the text of the cell you activated the macro upon (let's say you initially operated the macro on B5 cell -which has the text "montana" so it'll create "montana" subfolder also inside subfolder "happysheet")
4. Lastly instead of having only the text "montana" in "B5 cell" like we had initially before activating the macro, the macro'll also add to that text "montana" the link to "montana" folder (path of excel file->picturesAuto->happysheet->montana)
The way I'm planning to use this is to add a lot of HR pictures inside those folders created by the macro that related to that specific sheet, and that specific cell I've activated the macro upon. Plus just having those pictures so organized is just outstanding in every scale, and the excel file will keep running smoothly without any resource consuming overload.
Adding a search engine like feature to a spreadsheet we have which has multifarious columns of data on it, here is a screenshot of what it looks like:
[URL]
We have multiple sheets for each tutor like that picture which lists all information of their learners for reference, now I want to do a search feature on the main home page or perhaps on a dedicated "Search" page which allows us to filter out specific information out of those columns, mainly the ability to search by "Tutor" (aka sheet name), "Postcode", "Venue", "End Date", "Learner Name" and "EBS" number...
I have a table
item_desc
a
b
c
a
b
c
a
b
c
As you can see, a,b and c is repeated times.
How do you make it so that it would look like
item_desc
a
b
c
item_desc2
a
b
c
item_desc3
a
b
c
Is there a shortcut in doing this instead of copy pasting it?
I have a record just like this with about 572+ rows and 13 repeating items with that 13 items having their own column so copy pasting is really a tedious task. Kindly please help me solve this?
I have created a excel document. Would like to create report using the data.
create reports using excel data
The following code is used to export values form Excel into a word document:
The report obtained then looks like this:
Excelforum.jpg
How can I make the values fit within the margins of the word document?
I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.
Sample Data:
Code:
State Characteristic Value07 Value08 Value09
Alaska A 11 23 45
Alaska B 13 22 98
Alaska C 99 91 21
Alabama A 23 14 11
Alabama B 44 62 76
Alabama C 75 47 21[code]....
This code doesn't run at all.
How can I create keyboard shortcuts in the easiest way.
E.g.: I would like to create my own shortcut for "Merge".
I've used excel to do a lot of managing stuff as far as budgets. I'm not sure if this can be done or how. I'm not to great in excel, So perhaps you can lead me a hand. I'm starting out a home based business and trying to set everything up so I'm ready to officially start. I am planning on getting a scanner to keep track of what comes in and out.
So what I wanted to try was if I scan "A" 4 times I wanted the QTY to be 4 so basically group similar Items move on to "B" and oh crap I found 10 more of "A" scan that and have it updated now to 14. You catch my drift? so I'm not sure if a statement like {if "A" is scanned then the QTY would be +1} Then the sales part would be the opposite of that statement.
