Free Microsoft Excel 2013 Quick Reference

Copying data validation lists Results

Hi

I am wondering if anyone can help. I dont know if this is to easy to explain or not so ive attached an example spreadsheet.

Basically I run a reconciliation on a daily basis, which throws out differences on bank accounts. Our customer requires the format of their report in a neat format rather than two seperate reports seen in tabs (Proof and Open Items)

On the rec sheet, I have a list of static data which I will manually maintain. There are two vlookups. One to pull in balance 1 which represents one system and one in balance 2 which represents another system. In column H i have a simple formula to show the difference between the two systems.

For the differences, there can be instances where there is a valid reason and this information can be found on the open items list. I was hoping to pull this data from the Open Items sheet and add it to the main page by the means of using the account reference on Recs sheet Column B and matching it up against the name on Column B on the Open Items sheet. I only want to pull the cells shown in the example (EG - Comment, DebitCredit, Poolside, Amount)

If there is more than one item on the Open Items sheet, which relates to the account, I want to insert a new row on the recs sheet so that it looks similar to the example attached.

Can anyone help?

I am trying to create a simple macro that will run every time I open a
specific workbook in Excel 2002. When I look at the Excel helpfile on this
it tells me to start the Visual Basic Editor and search for help there. I do
that but find no help on this subject at all.

All I want to do is copy a long list of names from one workbook
("NameMaster" to another ("_PayrollSheet") for a validation list. I will
then copy the NameList to an area just below my data entry block to take
advantage of autofill.

I want to use the macro because the "NameMaster" workbook will change
frequently and I need the current list in the _PayrollSheet "NameList" every
time that workbook is opened.

Thanks for the help,
Carl

I am using Microsoft Excell 2003 SP1.

I have a UDF in a normal Module that reads all the worksheet names in
the active workbook and puts them into an array. A Summary worksheet
contains a formula copied to several cells in a column that uses this
UDF to list the sheet names. The formulas end in (wihout the quotes)
"&T(RAND())" to make it volatile so that it updates whenever worksheet
names are changed.

I also have a Workbook_SheetChange macro that writes data into an
adjacent cell when a target cell value is changed. I use
Application.EnableEvents=False at the beginning and
Application.EnableEvents = True at the end of the macro. The target
cell has a validation drop down list.

When the line of the macro is reached where it is supposed to write to
the adjacent cell, before the data is actually written to the cell, the
UDF is called and executes.

This is not a problem if valid data is entered into the target cell via
keyboard entry. The UDF executes and execution is returned to the
macro which then finishes normally. But if the data is selected from
the validation dropdown list, execution never returns from the UDF back
to the macro. The data never gets written to the adjacent cell and the
Application.EnableEvents remains False.

Is there anything I can do to keep the UDF from taking over the
execution of the code? The Application.EnableEvents = False does not do
it. Any other VBA statements that would do the job?

Thanks,

Larry

I have a cash flow spreadsheet that automatically selects different assets and data from a data validation drop down list. It's one self contained workbook with multiple sheets. I only need one workbook to create the cash flow easily, however I have to print a copy of each possible cash flow based on a combination of two variables. It would be great if I could automatically create the pdf's for all of the combinations. Printing them then would be easy by selecting them all in the explorer window and right clicking for the print option. Do I need VBA for this? Can I record it? Can you help?

Hi,

I am trying to copy (not transpose) a very long row of cells E2:DB2, to a column, so the top cell in the column =E2, the next =F2, the next =G2 and so on. When I try to drag the formula down it does not seem to want to follow the pattern i.e. advance the letter by one in the formula. Can this be done to save me having to do each one manually?

I am trying to do this because I wish to make a cell with a drop down list (i.e. data>validation) from the values in the row, however the option to 'ignore blank' which I require does not seem to work if my source list is horizontal, therefore I am making it vertical, can it be made to work woth a list created from a row?

Hello,
I was hoping someone could help me.
I am creating a spreadsheet to track the progress of volunteers within our charity.
I am effectively using Excel as a database, having created a list whereby each row is a seperate record.
I have several worksheets and want to move (ie cut and paste rather than copy and paste) certain records to another worksheet upon the condition that a particular cell in that record = yes.
I want Excel to automatically cut a row out of my 'Enquiries list' worksheet to the next available row in my 'Waiting list' worksheet if column N in that row contains the word yes (This value is entered into the record using a data validation drop box and not as a result of a formula, I mention this because my research so far indicates that using a Sheet Event may be the best way but that they dont work if the value is decided by a formula...handy to know but I have no idea how to create a sheet event in VB or whether a sheet event is best)

I am new to coding so was hoping someone could explain what the best (and easiest) method to do this

Hi guys, I’ve honesty tried to figure this out using VBA and Macros but my limited knowledge has me dumbfounded as to how to even start.

Please see the attached files:

Calendar.xlsSummary.xls

Calendar.xls is the source data whereby an employee opens the file and populates variances to the schedule on his particular day. If there’s a variance to what was scheduled, he selects from a dropdown the type and enters notes in the cell below (this comes from a validated list at the end of the sheet). Each day of the week for each instructor is in this calendar and is populated into a summary type table just below (to be hidden after I can wrap my head around this macro); I thought this would make it easier for copying onto master summary file) At the end of the week, an employee clicks on the “Populate weekly summary” button and if there’s a variance listed in D8:D45 then that whole row would get exported into the “summary.xls” onto a new row (would be first empty row I assume?).

I initially thought an ongoing vba could work for this however, changes are made to the specific variance throughout the week so a button to click at week’s end (run the macro from the button) would be better suited.

Thanks in advance for any help.. your assistance is greatly appreciated!

Cheers..

Big log with multiple international users, must be shared, some only have Excel 2003. Want to prevent overwriting data and getting items into wrong columns, and limit some entries to their own list. Some users have been keeping their own spreadsheet and then copying over into the main log, making a mess.

Simple non-VBA validation doesn't prevent overwriting, nor does it work when entering by forms. What I want is to ensure Data Integrity for metrics to report progress. Am willing to work with VBA but just a beginner with it, although I've created Access DBs some years ago.

Hoping to work with this over the weekend of 3-4dec11. Thank you for any help.

(Accidentally cross posted in old thread as well, not knowing then how to start a new one.)

Hello!

I have a cell which is defined as a data validation drop down list. It has around 400 article names in it. When one article name is selected, only this article name should be visible in the pivot table and the other 399 or so should be hidden. Because selections are made repeatedly in this file, the previously selected article group (or visible pivot item in the pivot table) is still visible in the pivot table when I select a new article group in the cell. To"get rid of" the previously visible pivot items, I wrote the following:

HTML Code: 
For Each pvtItem In pvtArticleGroup.PivotItems
        pvtItem.Visible = False
Next pvtItem
This doesn't work because one pivot item always remains visible in the pivot table (which is what I do not want). Therefore, I changed the above code to true...

HTML Code: 
For Each pvtItem In pvtArticleGroup.PivotItems
        pvtItem.Visible = True
Next pvtItem
Now the file takes 4 minutes to update a new article group because it makes all 400 pivot items visible (see above), before continuing.

Here is the complete code:

HTML Code: 
Sub OArticleGroup()
' Pivot Feld pvtArticleGroup wird auf neuen Filter eingestellt

Set pvt = Worksheets("Customer List").PivotTables("PivotTable1")
Set pvtArticleGroup = pvt.PivotFields("Ag Desc")
strOverviewArticleGroup = Worksheets("Overview").Range("G7").Value

On Error Resume Next
For Each pvtItem In pvtArticleGroup.PivotItems
        pvtItem.Visible = True
Next pvtItem
 

Select Case Worksheets("Overview").Range("G7").Value
   
    Case Is = "(Alle)"

        Sheets("Overview").Select
        Range("G7").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("H7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
  
    Case Is <> "(Alle)"
        For Each pvtItem In pvtArticleGroup.PivotItems
            If pvtItem = strOverviewArticleGroup Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If

        Next pvtItem
        
        Sheets("Overview").Select
        Range("G7").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("H7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        

End Select
End Sub
Can I write this code so that it is more efficient? Thank you in advance for your help!

I have a spreadsheet that users need to enter part numbers that we have quoted for. I need to prevent users from entering duplicates in column C. Data validation isn't an option since users copy & paste sometimes into that column.

The code works fine if user happens to copy & paste a part number already on that list. But for some reason, sometimes the code will work & sometimes it fails to catch duplicates when users type in the part numbers. Sometimes users will insert a row & type a part number & the code won't catch the duplicate part number.

Need help with the code to catch any duplicates in column C & sometimes the data in column C is non contiguous so the code has to check if the data entered matches any cell in that column. I would also like for the message box to also display the cell location were the original data is located.

Below is the code used & I also attached a sample of my spreadsheet.

Any help is greatly appreciated!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim ans As String

Const myCol As Long = 3

If Intersect(Target, Columns(myCol)) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each r In Intersect(Target, Columns(myCol))
   If Application.CountIf(Columns(myCol), r.Value) > 1 Then
   MsgBox (r.Value & " already exists")
       r.ClearContents
   End If
Next

Application.EnableEvents = True

End Sub


Hi,

For our attendance register, the master data table holds a list of all club members (one member per row).

The master data table consists of formula columns, data columns, and two data columns with drop-down list validation.

I'm using this code:

Sub PaintBlankRowMasterTable()

'  This copies the cell formats down throughout the table including the new bottom row
    Range("rngMasterTopRow").AutoFill Destination:=Range("rngMaster"), Type:=xlFillFormats

'  This copies the formulae down the relevant columns
    Range("rngMasterFormulaTopRow").AutoFill Destination:=Range("rngMasterFormula"), Type:=xlFillSeries

End Sub
to copy the cell formats & formulae down to a blank row at the bottom of the table to await a new entry (new member).

The problem is the two columns with validation. The drop-down list is not copied with xlFillFormats, and I don't want to overwrite any existing data by using xlFillSeries.

Is there a Range().Autofill "Type" that would copy the cell validation drop-down list without overwriting existing data?

If not, can anyone suggest an alternative VBA approach to set up the bottom row that would include the validation?

Cheers,

I'm trying to write a macro to use as an auto-update feature for a spreadsheet.

I currently use Data validation on an input cell to allow the user to select from a list of product types.

I'd like the update to affect this list, so new product types are always available to the user.

However, the list of product types is in another spreadsheet and contains numerous repetitions.

I've managed to get the following code together based on what I've used before and what I've found searching so far, but it seems to have a bug in it as it wont always work. I also need the original copying of the list to be done to either the new spreadsheet of a different page in the original spreadsheet, but every time I try this it causes an object error.

PHP Code: 
Sub Macro3()

'
' Macro3 Macro
' Macro recorded 05/12/2008 by Information Technology
'
    Workbooks.Open Filename:= _
        "T:SSTCCDEngineeringBackthin_dataPhotolithMASK_DETAILS.xls"
   Sheets("Mask List").Range("f4:f2000").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("a1:a3"), CopyToRange:=Range("E1:E2000"), Unique:= _
        True
    Selection.Sort Key1:=Range("E1:e2000"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        Columns("E:E").Select
        Selection.Copy
      Windows("MASK_DETAILS.xls").Activate
    ActiveWindow.Close
    Windows("Dry_etcher_log_B.xls").Activate
    Sheets("Calc Sheet").Select
    Columns("M:M").Select
    ActiveSheet.Paste
 End Sub 
Could someone kindly point me in the correct direction.

Thank,
Gavin.

Hi,

I have two worksheets, LETFILE and LETconsol, LETFILE is a data file that I create
a template for user to input data and LETconsol is a collect data file that I collect
data from many LETFILE(1,2,3....). The problem is I cannot store text from LETFILE cell E5 to variable and paste it to LETconsol. I create this text as a drop down list
from data validation for users to select which option do they need to put into this
cell. Could you please help me how to fix it ? Thank you.

<<<<<< Here is my code >>>>>>>>

Worksheets("LET").Activate

On Error Resume Next
Set Letsheet = ThisWorkbook.Sheets("LET")
Row = 5 'LET Row
iRow = 2 'LETconsol Row
Do Until IsEmpty(Letsheet.Cells(Row, 2))
Windows(LETFILE).Activate
Worksheets("LET").Activate
ChangeLosstype = Letsheet.Range(Row, 5).Formula

'at this point I try to change many properties eg. Letsheet.Rnge(Row,5).Text or
' .Value ...............It doesn't work the result is empty or it returns " ".
x = MsgBox(ChangeLosstype)
Select Case ChangeLosstype
Case ChangeLosstype = "Option 1"
Losstype = 1
Case ChangeLosstype = "Option 2"
Losstype = 2
Case ChangeLosstype = "Option 3"
Losstype = 3
Case Else
x = MsgBox("An Error Occur ...There Loss Type is not correct.....Please recheck")
End Select

'Start at Select Range C5:D5 from "LET" worksheet
Range(Cells(Row, 3), Cells(Row, 4)).Select
Selection.Copy

'Switch to LETconsol.xls
Windows("LETconsol.xls").Activate
Sheets("test").Select
'Start Select Range B2 from "LETconsol" worksheet
Range(Cells(iRow, 2)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range(Cells(iRow, 4)).Select

'paste text variable into cell
ActiveCell.Formula = Losstype
iRow = iRow + 1
Row = Row + 1
'End If
Loop

I am working on a system where the user can select a job and go to the sheet
where the details of that job are held. I have disabled the option to view
the sheets due to GUI design. I cannot change the sheet name to equal the job
name entered as text on a sheet . Below is the current code

Sheets("NewJob").Select
Sheets("NewJob").Copy Before:=Sheets(1) ' copying sheet "NewJob" The
copied sheet becomes NewJob (2)
ActiveWorkbook.Unprotect
ActiveSheet.Shapes("CmdSaveJob").Select
Selection.Delete 'deleting an object
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Range("C8:E8").Select ' where the name of the job is
Selection.Copy
Application.Run "Prototype2.xls!Addjob"

Sheets("Job").Select
Range("C29").Select
ActiveSheet.Paste ' copying name of the job to another sheet to populate
a combo box using data validation and a list
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B10").Select

Sheets("NewJob").Select
ActiveSheet.Range("C8:E8") = ""
ActiveSheet.Range("C10:d10") = ""
ActiveSheet.Range("C12") = ""
ActiveSheet.Shapes("Comments").Select
Selection.Characters.Text = ""
ActiveWorkbook.Save

Each time a sheet is copied the new sheet is named "New Job (2)" or (3) etc.
I either want a method of matching the job name on the sheet to the sheetname
or a method of changing the sheetname to the job name.

Any suggestions??? Would greatly appreciate it.

Nathan

Hello all,

this is what I'm trying to do:

I have a list of names (our total of 350 employees). I have listed them in a worksheet. This list is used for Data Validation and a drop-down list in column K.
I have copied 20 of those (our internal employees) into a column next to it, which is used for a similar drop-down list in column L.

In column B, I enter either "x" or "n/a", depending on the name in column K. If it is one of the internal employees (which are both named in the large list of 350 as in the small list of 20), I need the same row in column B to result in "n/a". If it is one of the external employees, I need it to remain empty.

I have tried entering a formula in a cell in column B like this:
=IF(K:K="John Johnson";"n/a";"")

This works, but I can't insert all 20 names like this. Who can help?

Thanks in advance!

at work we have workbooks full drop down (data validation) cells, that we populate manually depending on the data required on the day. Many times we have to select the same data from the drop down list in many cells 30 to 40, (not always in the same row, or column but many times every 3rd cell in a row or some thing like that)
What i wanted to know is if there is a way to select the cells i wanted to change then just change one and have excel copy the data to all the others, or any other ideas to speed up the process of having to manually choose information 30+ times.

Thanks for any ideas, leads or help

Hello,

I've created a spreadsheet that incorporates check boxes and I'm seeing strange things happening with them. I use the spreadsheet on a daily basis, by creating a copy of the previous day's version and saving it with new daily data. Every few days, the file will begin to behave strangely. For example, when I select a cell with a drop-down validation list, a check box from below (not always the same check box) will be moved to the cell with the drop-down list. It will then disappear when I move the cursor out of the cell, and does not go back to its original location. I can then select another cell with a drop-down list and the same check box will appear in that new cell. I also had notes in a few cells, and the same thing would happen with random notes until I deleted them. Each time this happens, I delete the new file and simply use an older version and update it with the new data.

On a side note, I have one macro in the spreadsheet, and also another function or two that I wrote in VBA. Thought I'd mention in case that is relevant.

Any feedback or advice would be greatly appreciated!
Thanks!

Hello,

I am working with budgets, for each item I have a Name, Unit, and Price per
unit then I just specify a Quantity multiply it by the Price per unit and get
a Total...

So far what I have is a Excel sheet with Name, Unit and Ppu, I made it to a
List, and defined a Name for the item's Name column, so each entry I add
automatically becomes part of it. Then on another sheet I used Data
Validation to choose from those Names from the List, the Unit and Ppu are
then taken from the "database sheet" with a VLOOKUP formula and then I just
enter a Quantity and get a subtotal.

It works great, but I would like to separate the database from the file, to
make the file lighter and to have other people being able to use it too. Does
anybody know how to:

A) Take that info from a Access database, so far my attempts copy the whole
database as a table to the Excel sheet, I would like to have it as I have it
right now, just not having all the database data inside the Excel file, or,

B) Take that info from a different Excel file, the problem with that is that
Data Validation is only allowed from the same file

thanks in advance

I am trying to use some named dynamic ranges as the source data on a line graph. For each series I have made a different range using =offset(etc.etc..).

Now if I try to use these named ranges as the values for a series on my line graph, I get the error "Not a valid function." Say the named range is "AnalyseSingData", in values I am typing =AnalyseSingData. If I define the same area manually by dragging the cells it all works fine. If I copy and paste the =Offset formula in to select the same cells as the dynamic range, I get the same "not valid function" message.

The reason I am doing it this way is that the size of the list of information goes up and down all the time.

Basically, is there a way of defining series values as a named range?

Thanks!

Andy

Hi you'll
I'm making a workbook (see attachment) for teachers to keep track of grades.
In the main sheet (Students) I have a list of subjects C5:C12, followed by a list of the students C15:C46.The following 8 sheets are named after the subject names, and the other 32 sheets are named after the students names.
Since these cells are used to rename sheets, I have a data validations in C5:C12 and C15:C46 that prevents the teacher from naming the subjects and the students, duplicate names, the name "history" and the use of []><* and ?.
The sheets are veryhidden when no value is entered in the above mentioned cells and unhidden when a value is entered.

Next to the subject and the student names I have textboxes that, when clicked, selects the sheet from the subject or student. These textboxes are named after the subject/student name and hide when no value is put in for the subjects or the students (sheet hides without value and when clicked, the debugger kicks in).

All the code to accomplish this is in the student sheet module.
The macros from the textboxes to select sheets are in module 1

The subjects have sub-subjects. These sub-subjects are copied to row A of the sheet of that subject, and the columns are hidden when no sub-subject is entered in the main sheet.
All the subject sheet have also the copied names from the students in Column 1, and the rows are hidden when no student name is entered in the main sheet.

This code is in the sheet module from the subject/students sheets. (In the attachment only for the first subject and for the first student).

All this hiding and un-hiding is done to make the sheets more viewable/workable and to make the whole workbook as user friendly as possible.

All code has Application.Screenupdating, but still the screen is flickering like crazy.
Has this to do with the amount of code, with the lenght of the codes (they are all very repetitive!!), the wrong module?
How do I make the code shorter?
Any guidance is highly appreciated.

MarcoAUA.


No luck finding an answer? You could always try Google.