Over 1 million tech questions and answers.

Excel data validation and executing a code based on selected value

Q: Excel data validation and executing a code based on selected value

Hi there
sincerely hoping & wondering whether someone out here could help me by offering me your solution or suggestion ?

My situation is as follows:
In my worksheet there is about 600 cells scattered all over the sheet that are to represent different monetary value figures, that I have used NAME MANAGER to define as MONEDATA1, MONEDATA2, and MONEDATA3 (each holding around 200 cells, which is the approx.limit allowed by NAME MANAGER for Excel 2010). Thing is, my work revolves around dealing with 12 different countries of currencies, and so all these monetary cells, in CURRENCY format, thus need to be 1 out of 12 different currencies with respective currency signs, at any one time I do some calculation. So, they must convert into 1 of those 12 currencies, each time determined by a selection from a single dropdown list of 12 currencies located at the top of the worksheet in one particular cell I data validated, which is $C$6.

Now, I just need the worksheet to convert all the 3 groups MONEDATA1, MONEDATA2 and 3 simultaneously, instantly when 1 of the 12 currencies is chosen in $C$6, by using a VBA code. Is there a best way to resolve this need ?

Could you help me with the above problem ? Thanks in anticipation and advance ! Greatly appreciated any help.
cheers & have a nice day there,
Jason.

RELEVANCY SCORE 200
Preferred Solution: Excel data validation and executing a code based on selected value

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel data validation and executing a code based on selected value

Read other 1 answers
RELEVANCY SCORE 100.8

Hi.

I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?

Cheers
 

Read other answers
RELEVANCY SCORE 97.6

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.
 

A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers
RELEVANCY SCORE 83.6

Hiyo.
To begin with, I just want to check if this is viable, and if so, which solution is best/easiest...

Office 2010.

Current situation:
An Outlook rule moves certain messages to an Outlook folder.
A macro button is used to run code that prints the attachments of all messages in that folder.
Think it then deletes the messages.

Required situation:
Instead of printing all attatchments, the macro needs to look at a specific column of data in an Excel file.
If a value in the Excel column matches (or part matches) the filename of the attachment, the attachment is printed.
All attachments with filenames that do not match the Excel list are left alone.

Alternatively, switch it round, so the macro is in the Excel file, and it checks the values in the column against the attachment filenames of messages in that Outlook directory, and prints those that match.

Alternatively, write something in Outlook code, perhaps using a form control, whereby the user can enter (paste in) the batch of values, and it then prints attachments accordingly, rather than trying to forge a link between Excel and Outlook.

Alternatively... use some 3rd party tool that accepts the batch of values and interacts with Outlook to print the attachments, bypassing the need to write and manage code.

Or something else?

Cheers.
 

A:Outlook/Excel - Print selected attachments based on Excel list

Alternatively... modify the existing Outlook macro to save all attachments to a dir on the PC instead of print, and then write an Excel macro to check the files in the dir instead of the Outlook folder.
 

Read other 1 answers
RELEVANCY SCORE 82

So far i have read you can't do this. So i'm wondering how you achieve this the correct way. I work for a gas field company and the database i am redesigning has a lot of fields that need validation, like well site names, company names etc. The person who built the original database had no validation at all, and you could find the word Production spelled 12 different way sometimes. Made it very hard to keep data readable.

So the part im working on now is the well sites list. I have a table called Well Sites. I have a main work orders table where they must choose a well site which is a lookup of the well sites table. This is fine and dandy, however. If they choose not to use a name in the drop down list, they can simply type anything they want, which is what i want to avoid. I want them to ONLY be able to type names that exist in the well sites table.

I was hoping it would be as easy as setting up a validation rule but from what google is telling me that is not even possible. So how do i control what user's enter. I do NOT want to type in a list of 600 well site names using OR for a validation rule. That is unmanagable, but i need to restrict what they can type so we don't get mis-spellings and mis-labeled wells.

Now i understand some things but some things i've just never done before prior to now. If i setup a one to many relationship between two tables then it seems to work like i want. Only values that exist in the other table can be entered into... Read more

A:Field Validation based on another tables data

Read other 6 answers
RELEVANCY SCORE 79.6

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only
Thanks
 

A:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?
 

Read other 3 answers
RELEVANCY SCORE 79.2

Can someone please offer some code to help me?!

Example of what I want to do:
I want buttons that will allow you to choose the page number(s) you want (1, 2 ,3, 4, or B), and then highlight them green when you click the button. It would be searching the range (K3:DA350), and then highlighting when it finds the value. The cell values in the range consist of
"1,2,3,4,B," "2,4,B," "3,4," etc, so it will have to find the value (1,2,3,4,B, etc) within the cells.

My goal is to offer the user the ability to choose any combination of pages ("1 OR 2"= all 1's and 2's are highlighted, "1 AND 2"= only cells with a 1 and 2 are highlighted, and so on).

What would be really great is if the cells are highlighted green when the button is pressed, and then return to normal when it's depressed.

Thanks for any help you can give!

Read other answers
RELEVANCY SCORE 74.8

Hi everyone,

I need help in excel, your comments will be highly appreciated.

I have 2 sheets in a workbook, one named: _Inventory_ & the other one _Quotation_

I wrote the inventory in Inventory sheet and then I went to Insert/ Name/ Define and created Inv.Quinc. which refers to =Inventory!$A$1:$A$2000

On sheet Quotation, cell H20, I went to Data/ Validation/ Allow: List/ Source:= Inv.Quinc. This is to create a list that I can choose from in H20
It works, but every time I open the list I have to scroll up, because it always start from the last empty field.

I have attached the file for your reference
Thank you in advance
 

A:Data Validation in Excel

Read other 13 answers
RELEVANCY SCORE 74

question,

I have a list of 5000 names below a data validation box. I would like excel to autocomplete the names that users are typing. unfortunately it will only autocomplete the first 1000. Is there a limit on the length of the list it will autocomplete or is this something i can change,

thanks for your time,

Dave
 

Read other answers
RELEVANCY SCORE 74

I am trying to write a macro to run through and change the data validation. I have a column and the data validation for a given cell is in the same column. Data validation does not carry out in autofill like formulas. I can not get the validation formula to work with a variable. ="=$columnvariable$number1:$columbvariable$number2" where column variable is the column letter and number 1 is a row number and number 2 is the other row number. Is there a way to make this work where i can increment the column letter and the numbers?
 

A:Solved: Excel Data Validation

Read other 16 answers
RELEVANCY SCORE 74

Hi,

Please see the attached pic.

In the workbook I am working on in the pic there are 3 pages

page 1. is what looks like the main document that is worked in and it has a drop down menu that I would like to add vendors to a preexisting list (but I can not figure out how)

page 2. is a list of venders mot NOT all of which are in the drop down menu already (if I edit the list the changes do not effect the corresponding vendor that are already listed in the dropdown menu on page1)

page 3. named "sheet 1" seems to be blank and have no function

** in the picture you can see that I highlighted the "VENDOR:" drop-down and right bellow that I am showing the data validation for that drop-down.

I would really like to be able to edit this drop-down list.

Can someone please point me in the right direct?

Let me know if you need any further information!

Thanks a million!
 

Read other answers
RELEVANCY SCORE 74

I have a spreadsheet for work that is basically free form. But in one column I have an ending line that reads "vehicle inspection, unloading" and a few more words. My former spreadsheet allowed me to select whatever line I wanted in that column in which to place the test by selecting it from the drop down. For some reason now it will do that but it will not let me type anything in any line. So, I can select that phrase from the drop down but I can't type any other info into any line with out getting an error. Kind of like it is either that phrase or nothing. How do I keep my drop down info on there and use it in any line I want while still being able to type anything into any line not occupied by the drop down text? Does that make sense? I set this up through data - data validation. I suspect there is just a setting in there I am not checking or unchecking properly.
 

A:Solved: Excel Data Validation

on the validation screen - you should see a "Error Alert" tab
on there will be a tick box
"show error alert after invalid data is entered "

untick that
 

Read other 3 answers
RELEVANCY SCORE 74

I am using Excel to create UPC bar codes for merchandise in my store. I have used the data validation function on the column with the UPC codes. It has been working great all the way down to row 778. Now no matter what number (whether used or not used) I put into the cell, I get the error that says that number has already been used. BTW I have tried numbers that I KNOW have been used. Does anyone know how to fix this?
 

A:Help in Excel with data validation countif

can we see a copy of the spreadsheet with dummy data in - this is a public forum, so any information posted will be in the public domain

have you tried setting up on a different sheet to see if the problem occurs on a new sheet
whats the version of excel you are using
how are you validating the column
 

Read other 3 answers
RELEVANCY SCORE 74

Hi everyone. I'm creating a spreadsheet using protection and data validation. I will be locking the sheet and allowing users to enter data in specific cells. The data they enter can be numeric decimals only. Protecting the sheet and ensuring the data is only numeric is not a problem. However I was wondering if there is any way to ensure the data is entered in increments of .25, .50, .75, and 1.00? For instance the user needs to enter the number 2.25 but accidentally tries to enter 2.20. Is there any way to ensure 2.20 cannot be entered?

Thanks for any help.
 

A:Data Validation In Excel 2002

Hmmm, well I guess one way I can do it is by using a named list for each cell. Any other way you guys can think of? Something that doesn't involve a drop down list?
 

Read other 2 answers
RELEVANCY SCORE 73.2

Hi

I am trying to write a formula in Excel data validation that meets one or the other condition.

I tried the following, but it does not trigger the validation pop-up box.

The formula is as follows:
=OR(SUM($E$11)<=8,SUM($E$11)<4)
i HAVE UNCHECKED "IGNORE BLANKS"

Hope I have been clear.

Jean

Read other answers
RELEVANCY SCORE 73.2

Currently I have a data validation drop down list in Excel where you can only select one list item. I want users to be able to select multiple items from this list, and have it show in the single cell separated by comma. I've read you can do this with VBA but I'm not familiar with VBA in Excel. If anyone knows another way to do this, or some step by step instructions you could link to about how to do it with VBA, I'm pretty technical so with a little guidance should be able to figure it out.
 

Read other answers
RELEVANCY SCORE 73.2

I have data validation drop down lists and conditional drop down lists in some of my fields on my Excel 2010 worksheet. When I switch to Data Form for easier data entry, it still restricts the cells correctly, but it does not show the drop down lists.

Is there a way to show the drop down list in the data form?
Can comments to help the data entry person be added to the form?

Thanks

A:Excel 2010 Validation in Data Forms

One thread in Office support should cover this> Excel 2010 Validation in Data Forms

Read other 1 answers
RELEVANCY SCORE 72.4

Any suggestions for removing blanks from an Excel 2000 drop-down validation list?
Data validation procedures I found at the MVP site work fine in a new (empty) spreadsheet but cannot get it to work in a spread sheet that already contains lists. Lists are named ranges in several columns but on the same worksheet. The data validation area is on a separate worksheet.
In one column, the drop down works fine but in another column the dropdown starts at the bottom and I must scroll up past blank lines to see the list.

Hope this helps:

Sheet 1 = columns that will contain validation cells
Sheet 2 = Named Range 1 in col. 1, Named Range 2 in col. 2 . . .

On Sheet 1:
col 1 dropdown list =OK
col 2 dropdown list = blanks and must scroll up to see beginning of list (only 3 items in this list)

Thank for any help you can provide.
 

A:Excel Data Validation Blank in Drop Down List

Your named ranges contain blank cells. Anyway you can change the range NOT to include them?
 

Read other 1 answers
RELEVANCY SCORE 72.4

hello everybody. its been a while since i have been on this site..anyway this is the situation:

i would like the number in cell A1 to increase by 1 everytime there is data entered in cell A2 and A3( data must be entered in both, reject and do not increment if data only entered in A2 or A3). any way to do this in excel?

thanks all!
 

A:Solved: Excel 2003 - how to perform some data validation..

Read other 16 answers
RELEVANCY SCORE 72.4

Hi There

I'm not sure if this is possible or not. I checked the internet and coud not find a solution. I have created a very simple dropdown list with only 2 entries. I typed in the entries in the list source box. Is there a way that the user can also type in any other entry of his/her choosing to the cell without the message appearing saying that the entry is not valid?? If it is not possible please let me know.

Thanks
 

A:Solved: Data Validation list (Excel 2000)

Just turn off error alert in Data Validation Option
 

Read other 2 answers
RELEVANCY SCORE 71.6

Hi,
I'm working on an Excel project of mine and wanted to use VBA to create a Data Validation List in a merged cell.
The strange thing is that using VBA for some reason the list does not show as a drop down list but as a long string
The string is the 26 letters of the alphabet
A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z
Click to expand...

Since I was doubting my own VBA code I decided to record a macro and added all the 26 letters of the alphabet
The resulting code:

Code:

Sub DVApply()
' DVApply Macro
Sheets("DataForm").Select
ActiveSheet.Unprotect
Range("B2:C2").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("D5").Select
End Sub

Now here is the thing. The moment I record the macro the list shows up nicely as a dropdown list, but if I run the macro the new list will show up again a a single string and not in a list unless I open the Data Validation Dialog and click Apply and close it.
I&#... Read more

A:Solved: Excel (2010) Data Validation using VBA not working as expected

Whatever the problem is I wrote another routine and it works now.
I've thrown every range at it and it (still) works.
I'ts still strange why even the recorded macro does not work when you run it again afterwards.
Let it be, one more of Microsoft's deep secrets
At least this post had 15 views, but I'm marking it solved.
 

Read other 1 answers
RELEVANCY SCORE 70.8

I would like to know if anyone has a simple macro that returns a true if a selected range is not contiguous.

The idea is, I have a workbook with one sheet for every month where tasks, vacation etc are kept and planned.
You can select a range or several ranges (see attached bmp file) and usign the Right mouse button it permits the user to enter a 'V' for vacation in that selected range or ranges

What I am looking for now is if in case the range is not contiguos I want to be able to determine where each different range starts and ends (that I can do with some code I already have for a contiguous range) but now I would like the code to return the three anges

"A4:B5" "C6:E7" "F8:J10"

Then I execute the appropiate code to enter, format or whatever each cell in the separate ranges, the actions will be same but skippng the not selected cells.

Maybe something like not in selection?

It's difficult to attach the workbook I'm using since I don't have an empty one at hand right now, but I'm preparing one.

It's easier to prgram than to explain what I want.

Thanks in advance for any tip
 

A:Solved: Excel: need vba code to determine if selected range is contiguous

Just for the info, I FOUND IT!

I found the following code: it selects one cells of the cells in de selected non cntiguous range

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If
End Sub

Now I added my code to loop until count < 1

Sub Unselectthem()
Do While Selection.cells.Count > 1
UnSelectActiveCell
Loop
Range("B5").Select
End Sub

and in the first one I will replace the red text with my code

If FullRange.Cells.Count > 0 Then
FullRange.Select <<< here my code to invoke the necessary module
End If

I just added this, maybe somebody else is / was looking for something similar.

Thanks anyway.
 

Read other 1 answers
RELEVANCY SCORE 70.8

Hey everyone,

I was wondering if anybody on these forums could help me with the below task?

I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).

At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:

Job name Monday 1st Oct Tuesday 2nd Oct Wednesday 3rd Oct Thursday 4th Oct
Job_xxx SUCCESS SUCCESS FAILED FAILED
Job_yyy SUCCESS SUCCESS SUCCESS RUNNING
Job_zzz MISSED MISSED CANCELLED SUCCESS

What I&#8217;d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.

Example:

- Monday 1st Oct, 10am &#8211; email comes into inbox with subject/body containing &#8220;Job_xxx has started &#8220; >>>> update Excel sheet to record RUNNING under column &#8216;Monday&#8217; in row titled 'job_xxx'
- Monday 1st Oct, 1pm &#8211; email comes into inbox with subject/body containing &#8220;job_xxx completed successfully&#8221; >>>> update Excel sheet to record SUCCESS, ... Read more

Read other answers
RELEVANCY SCORE 70.4

Hi

I use MS 2010, and in my Excel work book I have a list which works fine for me. But when I send it to others, the cell with the list in does work. - By not working I mean that there is no "arrow" to access the different variables that the list should show.

I assume this is a compatibility issue. Was just wondering if there were any known solution.

Thanks
 

A:Solved: List function in data validation not compatible with earlier version of Excel

Read other 14 answers
RELEVANCY SCORE 68.4

Hi all,

I am hoping someone has an answer for my dilemma.
I have an excel sheet where I enter times for 4 events (A, B, C, D).
I graph this as a bar graph on the same page.
This graph is then linked into a powerpoint slide that I display on a looping basis so the chart is always updated based on the excel worksheet.
I have figured out how to get excel to play a sound once a time is entered for event D but the sound does not play on the powerpoint slideshow when it updates. I think I need a new solution.

My goal is to have the powerpoint slideshow play a sound every minute once a time is entered for event D and then stop once the time is removed. It doesn't matter what type of sound file it is, I assume wav would be easiest.
Any help would be appreciated.

The excel sheet looks somemthing like this (sound only plays when a time is entered into row for D):
Event Time
A 4:00
B 4:40
C 6:25
D 7:00

Thanks
 

A:Playing sound in powerpoint based on excel data

As an alternate option, how about a way play a repeating sound in PowerPoint from a link in another document. The PowerPoint is locked so is updated from a linked doc.
I will settle for a way to start/stop the sound remotely.
 

Read other 1 answers
RELEVANCY SCORE 68.4

Change PowerPoint shape based on data in Excel. I need to change the color of a specific unique shape on a specific slide in PowerPoint presentation using data obtained from Excel.

issues are:
1. how to select the slide I want
2. how to select the unique shape object I want
3. how to use the data in Excel to change the color of that one particular shape object, not all the shapes of that type.

example is I use the numbers (1, 2, 3, 4) stored in Excel and when the number changes the color of the associated oval in PowerPoint is changed.
 

A:Change PowerPoint shape based on data in Excel

If you have a powerpoint presentation I imagine the shape you mention is an imbedded shape and linked to Excel, well, then you don't need to know which slide and the unique object, you let Execl do that and the imbedded shape will change when Excel is changed and refreshed.
 

Read other 2 answers
RELEVANCY SCORE 68.4

I'm currently trying to figure out how to send emails to specific people based off data entered into an excel spreadsheet. Some basic information is that I'm on a mac, use excel 2004, and I'm using the basic mac mail program (have a .comcast and .edu email account). Hopefully what I'm trying to achieve is pretty simple. I would like to do the following:
Daily send an automatic email reminder every morning to specific people.
This email needs to send recipient x an email that reminds them to do chore y on the current day z.
So when the current day z is today I want the email sent out in the morning.
I would like this to be done automatically but if that is really complicated some type of macro would be awesome.

So basically the excel sheet is going to have column 1 with the due-date, column 2 with chores, column 3 with names, column 4 with emails.

I know basic computer knowledge.

I would really appreciate help on this! Thanks in advance!
 

A:Microsoft Excel - Sending emails based off data

Try this code:
Sub xlDialogSendMailWithMessage()

'Note: This sends the ActiveWorkBook as an attachment
'using the default email program. If the workbook to be
'sent is not the active workbook, then insert code to
'open and activate it before running this...
If MsgBox("Send message now?", vbYesNo, "") = vbNo Then Exit Sub
Dim N As Long

With Application

'scroll down to the message field
For N = 1 To 5
.SendKeys "{TAB}", Wait:=True
Next

'insert text (replace 'Email text here...' with your message)
.SendKeys "Email text here...", Wait:=True

'replace [email protected]; [email protected]; with your recipients
'replace 'Insert subject here...' with your subject
.Dialogs(xlDialogSendMail).Show _
"[email protected]; [email protected];", _
"Insert subject here..."
End With
'
End Sub

You will have to look at the code and do some coding.
 

Read other 3 answers
RELEVANCY SCORE 68

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

A:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

Read other 3 answers
RELEVANCY SCORE 68

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

Read other answers
RELEVANCY SCORE 68

Hi,
I have an employee list excel file (vacation) including the details about empno, emp name, last vacation, next vacation, etc in sheet 1.
Now, I would like to write a macro where I will enter the name of the month in a dailog box and the macro should display the list as in sheet 2 of the attached file.

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

A:Solved: Excel Macro to display data based on month

I've made a quick macro that does as you requested. It is not "smart" at all so if you change where the data is located (by adding a column for example) it will no longer work. It will handle more rows of data no problem. See the attached.
 

Read other 2 answers
RELEVANCY SCORE 68

Hi All,

Firstly, thanks to everyone reading, and greater thanks to those replying!

Here is my best attempt at explaining my problem...

In Sheet 1 I have data of "invoice number, "name", "invoice date","status", "amount", and some other headings. Everything is straight forward, except "status" will be either "outstanding", "paid" or "donation".

The headings are on the top horizontally, and the data is all vertical from that.

On Sheet 2, I need to return all the rows (the entire row's data), but only for those for which the status is "paid", and then on sheet 3 I need to return all the rows (the entire row) for those in which the status is "outstanding", and then the same applies for "donation" on sheet 4.

Currently I am using this very simple formula, =IF(Invoices!D3="outstanding",Invoices!A3,"") and then =IF(Invoices!D3="outstanding",Invoices!B3,"") in the next field, and the formulas go on like that en every cell to the right for each column on sheet1.

I have been using this just to get by, but as you can well imagine it is not very elegant, and since the rows on sheet 1 keep on being added downwards, I keep on having to copy my formulas down on sheets 2, 3 and 4, or I have to copy them down to like row 2000 just to keep ahead.

Is there a more elligant way of doing this - I have looked at vlookup, but unless ... Read more

A:Excel - Return Data on seperate sheets based on value in first sheet

Read other 6 answers
RELEVANCY SCORE 68

I've tried goggling this a bit and can't seem to find exactly what I'm looking for.

I'm trying to setup a sheet that can run calculations based upon what someone else enters (ex: if they select 'option1', preferably from a drop down list, then two values will be set to the corresponding values, ex: '5' and '6' or whatever they may be). This would preferably actually be within a formula, so say if they have selected an option, it will perform the calculation with the appropriate data, without having to display it on the current sheet.

I was also trying to keep all the data on another sheet so the one I was actually using looked a bit more organized. I've never had to do anything like this in excel before and was looking for some help or a push in the right direction.

A:Excel 2010 - Accessing data based on conditions from other sheets

I have found the 'CHOOSE' function very useful.
Very difficult to explain, but here is Microsoft Office attempt... CHOOSE function - Excel - Microsoft Office

My scenario was to have a cell in which you would select Quarter 1, 2, 3 or 4. The values in the Profit & Loss Account would change accordingly. Here are examples of my scenario (the cells highlighted in the spreadsheet on the right refer to another sheet - CY Actual - in the workbook).

I hope this helps.

Read other 2 answers
RELEVANCY SCORE 68

Hi Everyone
I'm needing to automate a process that notifies a group of users to complete specific data on a shared Excel template file. The process is date driven. For example; row 1 has data due on 4/25/2017 by Bob Smith, while row 2 has similar data due on 4/29/2017 by Rose Hill, etc. We are in a Windows 7 environment, Outlook 365 mail clients and recently updated to Office 2016 from 2010.
Anyone have any ideas on how to best approach a solution for via Excel macros?
 

Read other answers
RELEVANCY SCORE 68

I would like to know a method to solve an otherwise repetative task in Excel for me.

In my Excel spreadsheet, I have many columns relating to customer orders etc. Each customer (finite number (~40)) has its name (customer1, and customer2 etc) input to a cell, I then must fill out the customer account number (ac001, ac002 etc). The customer name and number already exist on another sheet.

How can I automate the process of when I input the customer name, excel then fills in the customer number in the next cell?

Many thanks
Dave
 

A:Excel: Conditional Cell Input Based on Entered Data

Depending on how your Customer data is arranged, look at either Dlookup or Vlookup.
But Access is better for Customer databases.
 

Read other 3 answers
RELEVANCY SCORE 68

Hello experts

I have an excel document with many sheets starting with sheet2

*** column_headers "enquiry#" in B6 and query_expiry_date in E6 (of every sheet)

What I am looking to do is, using sheet1 (which is unused at the moment)
dynamically return 3 pieces of information (in 3 columns, A,B,C for example)
based on expiry dates.

ie: if the expiry_date<today() then return sheet#, enquiry# and the date that expired.
this is for each enquiry and each sheet and if I can sort them in order of expiry date, that will be owsome.
Can that be done using lookup? or maybe better in VBA? a push in right direction or example
of code snippet is well appreciated.
Thank you
 

A:Solved: excel(VBA) returns data based on expiry date

Read other 12 answers
RELEVANCY SCORE 68

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,
 

A:Solved: excel - data returned based on the value of a specific cell

Read other 10 answers
RELEVANCY SCORE 67.2

It's difficult to describe my situation in words, so I'll just show you what I'm trying to do.

I have a table that looks like this:

A, B, C
Name, Hours, OT hours
Jane, 166, 0
Jack, 163, 17
Mike, 168, 0
Dot, 167, 24
Erin, 164, 8
Carmen 158,0

and I want to create a list that will have 2 rows if there are OT hours, and 1 if there are not.

A, B
Jane, 166
Jack, 163
Jack,17
Mike,168
Dot, 167
Dot, 24
Erin, 164
Erin,8
Carmen, 158

I can't use macros or VBA. It has to be formula driven. I can use a helper column or two. The starting table will change depending on who charged hours where. I've been trying to use IF and OFFSET and ROWS to do it, and it works until there's 2 consecutive rows that have OT hours.

Just to clarify, I just need to know how to get column A listed properly elsewhere in the spreadsheet - I can derive Column B using vlookup.

Thanks so much!
 

A:Solved: MS Excel: create list based on data in a simple table

2 Formulas - 1 for finding the name, the other for finding the appropriate value.
Note - must start on row 3 or greater.

Names (mine start in cell G3): =IF(G2<>"",IF(G1=G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1),IF(VLOOKUP(G2,$A$2:$C$7,3,FALSE)>0,G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1))),$A$2)

Hours (mine start in cell H3): =IF(G3=G2,VLOOKUP(G3,$A$2:$C$7,3,FALSE),VLOOKUP(G3,$A$2:$C$7,2,FALSE))
 

Read other 3 answers
RELEVANCY SCORE 67.2

I am fairly new to VBA and this is my first post so please be patient with me. I require the assistance of other vba programmers in coding the following:

1. The user selects a Report ID from the first List box "Report ID's", clicks the "Add Single" button, then clicks the "Print Report" button. - (I have coded this)

2. The code for the print report button needs to take the report id from the "print report" List box and looks in the "Report ID" column from the "Reports" sheet for a match. Once found, it grabs the corresponding compilation no. for that Report ID.

3. It then uses that compilation ID no. to search in the "Compilations" worksheet for a match; it then grabs the corresponding Keyword for that compilation ID/no.
4. Lastly it uses the keyword to search for a match in both "Keywords1" and "Keywords2" columns from the "Exhibits" worksheet. Once a match has been found in any of the columns, it then grabs that entire row/exhibit and prints it out in it out in a word document, with correct formatting, Labels and headings, for the user to edit.

I would appreciate any assistance that can be given and strongly emphasise that i am not looking for someone to code everything for me, instead to take patience and go through a process step by step if possible.

Thankyou to any users who provide assistance, your patience and time taken to help has been duely noted and much ap... Read more

A:Solved: Create a report in word based on excel spreadsheet data

Read other 16 answers
RELEVANCY SCORE 66.8

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

Read other 1 answers
RELEVANCY SCORE 65.6

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

Read other 3 answers
RELEVANCY SCORE 62.8

I have a spread sheet where I would like a message to pop up after I enter a value in cell G2 based on what the value of I2 is. like the following description:
In cell I2 I have "=(E2*F2*G2)/144" after I enter a value in cell G2 I want a message to come up if the value of I2/g2 is less than three and not equal to zero" Is this possible? If so could someone help me out with this?

thanx
 

A:validation based on another cell

This is the same as the challenge file but I added the code for this question
 

Read other 1 answers
RELEVANCY SCORE 61.6

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 61.6

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 60

I was trying to speed up my comp and was going through temp files when I found several that I could not delete

I opened them with notepad to find a lot of encrypted jargon and several instances of what appears to be scripted code

I copied one section of it to paste below:

  A d o b e P h o t o s h o p  A d o b e P h o t o s h o p C S  8BIM    ??1http://ns.adobe.com/xap/1.0/ <?xpacket begin='?' id='W5M0MpCehiHzreSzNTczkc9d'?>
<x:xmpmeta xmlns:x='adobe:ns:meta/' x:xmptk='XMP toolkit 3.0-28, framework 1.6'>
<rdf:RDF xmlns:rdf='http://www.w3.org/1999/02/22-rdf-syntax-ns#' xmlns:iX='http://ns.adobe.com/iX/1.0/'>

<rdf:Description rdf:about='uuid:45f59f42-e628-11da-8a15-fa212eb52135'
xmlns:exif='http://ns.adobe.com/exif/1.0/'>
<exif:ColorSpace>1</exif:ColorSpace>
<exif:PixelXDimension>1</exif:PixelXDimension>
<exif:PixelYDimension>31</exif:PixelYDimension>
</rdf:Description>

<rdf:Description rdf:about='uuid:45f59f42-e628-11da-8a15-fa212eb52135'
xmlns:pdf='http://ns.adobe.com/pdf/1.3/'>
</rdf:Description>

<rdf:Description rdf:about='uuid:45f59f42-e628-11da-8a15-fa212eb52135'
xmlns:photoshop='http://ns.adobe.com/photoshop/1.0/'>
<photoshop:History></photoshop:History>
</rdf:Description>

<rdf:Description rdf:about='uuid:45f5... Read more

Read other answers
RELEVANCY SCORE 59.2

I was wondering if it's possible configure a Windows 2019 IIS v10 hosted Web Server to perform OCSP checking of client certificates that are used to authenticate?

It is my understanding that typically the Responder URL that the Web Server contacts in order to validate the client cert is extracted from the AIA attribute in the client certificate. But is it possible to override/supplement this with an additional Responder?


For instance, what if I set up an OCSP Responder in the same domain as the Web Server and associated its revocation configuration with the SUB CA binded to the IIS Site. Now if client certs come in for authentication  and have an unrelated OCSP Responder
in their AIA, can I somehow tell the Web Server to check also the aforementioned Responder that has been stood up in the domain?

A:OCSP Based Validation for Client Certificates Using Responder Defined by Web Server

Per a reply from Mark B. Cooper at PKI Solutions this is indeed possible. You must edit the following GPO in order to override the default behaviour of the web server which is to only check the Responder URL specified in the client certificates' AIA extension.
Default Domain Policy > Computer Configuration >  Policies > Windows Settings > Security Settings > expand Public Key Policies
Once a custom Responder is specified in the CA / SUB CA's revocation properties the above GPO will allow it to check that custom Responder URL first, then ocsp as defined in the AIA extension and then CRLs
Thanks Mark!
EDIT:
Will post reference links once MS verifies my account.

Read other 1 answers
RELEVANCY SCORE 59.2

Hello everyone!

So, don't pay attention to my computer stats, as now I own a Macintosh, however, I've been having problems getting a Perl code to execute in Firefox, or any other browser on my computer for that matter!?!?! I'm so confused, because on another computer that I use a remote server, it's able to read it.

So this is what I do.

I make the Perl code in the terminal using Vim:

#!usr/bin/perl

print "Content-type:text/html\n\n";

print "<html> \n";
print "Welcome to the world of Perl! Please enjoy yourself \n";
print "</html> \n";

and then I save the file as hsimmon.2.2.pl and exit out of Vim, and run the code at the command line, and this is what I get:

Content-type:text/html

<html>
Welcome to the world of Perl! Please enjoy yourself
</html>

Next, I move to the browser (firefox), and I type the location into the address bar as such:

file:///Users/harrysimmons/root/perl_backup/hsimmons/S2.2/hsimmon.2.2.pl

Then, the output of the browser does not execute the code, it just displays all of the code in a plain text format. Is it because it's not running through Perl first? How am I able to make the code run through Perl first, and then submitted to the browser?

Any help would be appreciated.
 

A:Firefox not Executing Perl Code

Firefox does not run Perl code (nor does any major browser). When making webpages using Perl and other server-side scripting languages, the Perl code is run on the web server and then sent to the browser from the server. So you either need to set up a web server on your machine to test the perl code, or get some online hosting service where you can upload your files.
 

Read other 3 answers