I am trying to assign rooms to tenants. I would like to set up a function that reads a tenant's top 5 choices for a room, and looks up a column to see if the choice has already been selected before moving to the next choice. Then if all 5 choices have already been selected, the cell reads error.
This is how it would look : Excel Pic.jpg
have six different locations in a city in which I have 6 beds for people with psyhiatric problems to sleep in.
The managers of the different location send me whenever somebody new comes or somebody leaves the following information:
Name of client
Start date of stay (when new clients arrive)
End date of stay (when client leaves)
I have to keep all record of clients that have used our locations, so the list gets very long during the year and it is then very hard to see which beds are still available.
What I would like to see in a separate sheet is which location still has how many beds available. I then mail this overview weekly to organizations that assign people to my locations.
I want the user to be able to choose a printer but not print at that time.
1. Store the active printer as a variable using application.activeprinter
2. Have a dialog box (the printers available) popup and have them choose one, then store that
3. I know how to do the rest, but I will have the word document print out with the selected printer, then use the application.activeprinter reset to the original one
Is there a function in Excel that would randomly select one of the 4 specified numbers?
View 13 Replies View RelatedI am working with a spreadsheet that has quite a few products and each product has a column for each vendor of that product with the price associated with that vendor. So, I have five columns total:
product, vendor 1 price, vendor 2 price, vendor 3 price, vendor 4 price. I want to have a 6th column that will show the preffered vendor, which of course is selected by the lowest price. I have been able to accomplish this by chaining IF statements together, but it is getting VERY cumbersome for more than 2 vendors.
Ideally, I would like to tell Excel to look at the 4 price cells, choose the lowest one, and return a column number, which I can then associate with a vendor.
I'm trying to set up a consolidated spreadsheet with a number of tabs and a master sheet to work on. I want to set up this master so that I can type the sheet name into one box and in the next box it will use the sheetname I've just typed in to then pull the array off that sheet. I'm using this with VLOOKUP but when I try and type A1! in in the array section, it doesn't seem to like it.
View 3 Replies View RelatedIn my program I want some labels to be printed on printer P661 (black/white) and other labels on printer P656 (color).
Q: is it possible to do this in vba for my code?
The labels are made in Word and printed from within Excel.
Sub PrintNACL_LABEL()
Dim oWord As Object
Dim sPath As String
Dim iCnt As Integer
sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc"
iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1))
If iCnt >= 1 Then
Set oWord = CreateObject(Class:="Word.Application")
With oWord.Documents.Open(sPath)
.PrintOut Background:=False, Copies:=iCnt
.Close False
End With
oWord.Quit False
End If
Set oWord = Nothing
End Sub
The specific printers in the network are:
Color ---> "\PlantijnP656 op Ne00:"
Black/white ---> "\plantijn2000oldP661 op Ne02:"
I'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.
View 9 Replies View RelatedI have attached a sample of what I am trying to do. What I want to do is to be able to go through each day sheet and choose the name and times from the list box's I've created and have it show up on the summary page to match. So if I'm scheduled to work on Wednesday at 9am... I want it to show up on the summary page under Wednesday at 9am that I am working, but If I am not working I want it to be just an X to show up.
View 5 Replies View RelatedI have not located anywhere an answer how to use in vba dropdown list, when you choose a cell it displays in the TextBox the cell right to it? How is it done by using VBA? Here's a picture about what I am trying to accomplish:
View 6 Replies View RelatedI would like to get some help on choosing the right files through vba codes. I have the dates in cell a3 and b3 and all my files ends with the date. The user inputs the date and the code should get the data within that date.
I would like to get some thoughts on how to get the value of cell a3 and b3 and open only those files from the folder.
I'm looking for a way for excel to output data to a cell dependent on what I choose from a drop down menu.
For example. I have a drop down menu with 3 options. 1,2 and 3. What I want is that if I choose option 2, then in cell A2 (or whatever) would output the value of 2, say "2222". Or if I choose 1, then A2 would output the value of 1, say "12345".
How to open or Print a doc or pdf file from a combobox on a userForm
I have a folder full of doc files. I used a userform on which i put a combobox which contains those files' names and a cmd to open the file.
I managed to copy all the files names to the WorkSheet and listed them in the combobox.
@@@ I need a VBA code to do the following :
1- looking for the file name which matches the one i choose from the combobox
2- if there is a match, show " match" on a lable.
3- when i click the "open" cmd i created on the userform, it opens the file. or even print it and close it
4- prepare for another match.
I need to choose the content of a cell by the calendar date ( or system date). Before the date, I need the cell blank (or a 0 would be acceptable). On or after the specific date, I need to have a number appear in that cell. For example, what I need is: Before 10/20/2008, cell B5 should be blank. After this date cell B5 should be 8.
If I have a very long password would this make unprotecting the sheet harder?
Or do the programs available on the net extract the data from else where - Making it pointless choosing a complicated password?
I'm trying to create an invoice with customer list, and the problem now is I'm trying to find a list-dependent data way to input my customer's address.
At the moment, all I can do is a dependent dropdowns from a sorted list (see method here). So it's kinda troublesome in such that my list (of customers) opens up a list for address that has only one option (since there's only one address for each customer).
I hope there's a method out there whereby I can have a list of customers, then select the customer I want, and the address of this particular customer will appear in another cell below automatically (without the need of selecting it in another list).
My address is actually split into three different cells - Address, Building & Postal Code.
I'm trying to find a way to make the user select only one of five columns in a row. What I’m trying to get is a ranking of 1 to 5. The user will click a cell and it will fill in or the user puts an X in it. In row 8 I want to select A through E but only one of them. If the user changes their mind and clicks another cell, the first cell clears.
View 9 Replies View RelatedI'm currently trying to put together a search function of sort onto an excel document to look for entries of "Y" based on the choices in a drop down list to populate a list of names that have a "Y" next to them. I've searched the net and read up on IF, VLOOKUP and HLOOKUP commands but I can't quite string something together. An example of what I am trying to achieve is below;
Raw Data:
type1type2type3type4room1room2room3room4
AndyYNNNnYNY
BillYYNYYYYY
ChrisnYNYYYYY
DaveYNNNYYYY
EddyYNNNYYYY
FrankYYNYYNNN
GilesYYNYnYNY
HarveynYNNYNYN
IannYNYYYYY
JohnYYNYYYYN
KyleYYNYYYYY
Search drop downs:
Typetype1
Roomroom1
End result (based on the example of Type1 and Room1):
Bill
Dave
Eddy
Frank
John
Kyle
Im looking to create a macro that goes to a specific sheet on my workbook upon selecting an option. Example being I open the spreadsheet and a list of options come up such as:
BRIAN
JOHN
JAMES
PAUL
STEVEN
Upon selecting one of those names you are transferred to the sheet named "BRIAN", "JOHN", "JAMES" etc etc etc
If we look at Produced date and TO Date in Sheet1. If the TO date is less than the Produced date then the TO date is the date to use. i.e. for May it will Say
Produced:, 22 Jun 2009 "From:","2009-05-01","To:","2009-05-31". So use "From:","2009-05-01","To:","2009-05-31"47 (because TO < Produced use TO )
However in June it might say Produced:, 22 Jun 2009 "From:","2009-06-01","To:","2009-06-30". So use "From:","2009-06-01","To:","2009-06-21" (because TO > Produced use Produced -1 ).I've already have some formulas in place. Look at the attached file, but I don't know how to set up the IF formula or any other.
I have an excel sheet with a 'Home' and 'Data' page. I have imported data from an access query into the 'Data' page where I then push a button on the 'Home' page to run a macro on it. I have multiple queries in my database that I would like to be able to switch between in excel to run the macro on. Is this do-able without having to have multiple sheets?
View 1 Replies View RelatedOn my user form, the cursor blinks in the second text box down.
How do I change it to be blinking in the first (top) text box? This is the first box that data will be entered.
Is this an option in the properties box of the textbox?
I want to create a UI form to allow users to upload data. The data will be tabular (1 column per data field, one row per item). There will be more fields than I need, and the fields will not be named, so I need the users to select which column each field is in (with up to 20 fields). What do you think is the best control / combination of controls to use for this.
View 3 Replies View RelatedI am trying to calculate phone call rates based on several different conditions. *if my phone call has a "DESCRIPTION" of "Local" then I want to add 2 cents onto the "COST" *if my phone call has a DESCRIPTION of "Mobile" then I want to use the formula = SUM(G7*24*60)*0.4+0.1 to calculate the charges *if my phone call has a "DESCRIPTION" of "13 Number" then I want to add 5 cents onto the "COST" I am able to use a formula that allows 2 condition but I have been unable to work out a formula where I can add more conditions.
I have attached a spreadsheet
When I try to insert an 'option' box in which either a check box or radio button are used, only one option can be used. For each option I have to create a new option. But then, each option can be selected, which defeats my purpose. I want to create an option box where there are two option in the box, but the user can only select one of the options, not both, or neither.
View 2 Replies View RelatedThere is any way to select two item one from listbox1 and anotherr from listbox2 and paste on sheet
View 9 Replies View RelatedIs there a way to have a column unhide one choosing a certain value from a dropdown list (validation)? I want the column to be hidden throughout the worksheet until a specific value is chosen in the list. Once it's chosen then I want it to unhide that column.
View 4 Replies View RelatedI have created an excel file that generate outlook emails ( using Ron de Bruin VB codes)
I have a one more task to do with that.
I have list of contents in a combo box... those contents are short form of the main contents that i want to be in the mail.
if i select an content from the combo box and click mail macro it should open an mail with brief contents corresponding to the short content i selected in combo box.
My problem is in this defined range in "C30,D30,E30, F30 & G30" there is a possibility of more than one entry. If that is the case the entry would be in "C31,D31,E31, F31 & G31" If this is the case I need to pick the later which is "E31, F31 & G31" not
"C30,D30,E30, F30 & G30". How can I do that?
I am working on a grading sheet for our company. I would like users to choose from an option button choice (A, B, C, D, F) for each grading category and I would like to write that to a sheet that corresponds with the graded person. The form will have next and previous buttons to move through the grading process - when previous is chosen I would like the option buttons to populate what was stored in the data sheet. I think I can mottle thru the code individually but with 60 option buttons it may take quite some time.
Can I call an option button choice by "Group"? I started coding individually, but it will take a lot of code - surely there is a more efficient answer. A picture of my form is attached at the bottom of this post.
VB:
Private Sub OptionButton1_Click()Dim Grade As String
If Me.OptionButton1 = "A" Then
Grade = "A"
Else
Grade = ""
End If
untitled.jpg