Is there a way to make excel advance to next lowest cell automatically once a number of a specifed length (e.g. single digit integers in my case) is entered in a cell? I have large volumes of data to enter--usually integers from 0 to 9--and the extra "enter" keystroke becomes very tedious and increases errors.
View 9 RepliesExcel Sheet Description:
My Excel sheet is a check list inventory and is arranged in three, two column matrix.
The cells are expecting an integer rating (1-5).
Problem Description:
What I would like to do is enter my integer rating in a cell in a column and then automatically have the cell selection advance once to the right and then down to the next row until all the cells have been filled with there integer ratings.
I would like to do this without having to hit the enter or tab key.
How do i auto advance a text box in a user form to the next question. As long as that question was answered otherwise it doesn't advance and displays a msgbox telling them that something must be entered in that box? All done in vba.
View 2 Replies View Relatedstarting on A5, I have to entered 10 digits code. I would like to put a VBA code that after I entered the 10th digit it will jump down automatically to next column (A6) without hitting the ENTER key..and so on...
Then once I click on Save button, it will save it as a CSV file based on what I put in A1. ie: if I put in zzz on A1 it will save it as zzz.csv (pure csv).
Here is a copy of my code and I am Having a problem trying to get it to filter zero balances out. There are positive and negative numbers and I need to post them to a seperate sheet and exclude all zeros.
View 7 Replies View RelatedI'm trying to mark up in advance the dates where i'll get paid. I get paid monthly on the 2nd last working weekday of the month. So because we have the 30/11/2009 falling on Monday of next week, my pay day for this month is Friday 28/11/2009.in December it should be the 30/12/2009.
I'm getting all tied up in Networkdays, EOm and weekday function!
Is it possible to use an array without looping through it? I have code that has two loops: One that is just for the array and the other that loops through the data. It would be nice if I could get it down to one loop, but I'm not sure if it's possible. For example, if you have
Dim varArea As Variant
varArea = Array(1,2,4,6,8,12)
For x = LBound(varArea) To UBound(VarArea)
Do While Not C Is Nothing
Code here
Is there a way to advance to the next item in the array without using the loop.
I just came across an error in one of my workbooks where I had duplicate headers on a few columns
So When I used AdvancedFilter to " cut" the data into several workbooks for end users, the data in the first column was pasted 2x and the second columns was ignored altogether.
Headers since changed and that fixed the problem.
I'm just curious why the second column was ignored altogether
I have a excel sheet auto generated by a process in which there are 4 columns and 150 Rows. One column has dates data in DD-MMM-YYYY (15-May-2007) format..but the datatype of that cell is not Date. When I click on any date let say I selected 24-Mar-2007 in formula bar instead of displaying 3/24/2007 it displays as it is 24-Mar-2007.
But when i double click on this cell it shift to right alignment and display date format 3/24/2007 in formula bar. I have to create a formula using which I can see only some rows of define date range. Means if i define range in two cells 01-Jan-2007 and 01-Jun-2007. The sheet will display only rows having this criteria. But due to this datatype problem before putting the criteria I have to double click each of the row to convert it to Date. RightClick Format cells option is also not help full in this.
I'm trying to use the advance filter for multiple criteria in one column. It works fine if the criteria is ="*10*" (ie where the cell in the range has 10 in the string) plus other = criteria. What I cannot get to work is where the range doesn't equal 10 in the string. I've tried ="<>*10*" and other variations to no joy.
View 4 Replies View RelatedThis is a sort-of bizarre query I have.
I have a large array of data which I'm filtering out and copying to a new spreadsheet using an advanced filter. I have 2 filter criteria, one works... the other doesn't.
The second criteria is supposed to filter out rows that have blank cells in column C or D. I tried various things:
a. at first I tried following:
Column header: Name |Surname
Criteria: <>"" |<>""
b. since it didn't work I tried that
Column header: Name |Surname
Criteria: =<>""""" |=<>"""""
I also tried <>0 and <>null with absolutely no joy.
c. once I figured out that doesn't work either, I tried:
Column header: (empty)
Criteria: =OR(NOT(ISBLANK(C2)),NOT(ISBLANK(D2)))
now, the last one did have some effect, most of the empty-celled rows have been filtered out. However the filter persistently picks up ONE row that has blanks in column C2 and D2. I checked the data to check if the cell is really blank and it's as blank as it gets.
Obviously, I need help. It's been 3 days and I still can't figure out what's wrong with my filter. Is there a way of fool proofing the criteria, so that it will filter out all cells that are or appear to blank?
I'm having an issue where advnace filter isnt working and I need to get all of the unique entries.
I don't know if this has anything to do with it but I got some erros saying the numbers were being pu as text so I changed the cell tpe to a custom with 00000000 because the unique number I need to filter is 10 digits long.
As another alternative I would paypal some one a few bux if they can help me just compare two lists and make a report with the number of netries that show up in one list and not the other and vice versa.
I have many large arrays of climate data. I am trying to find an array criteria formula that would filter out those years from a variation around a given year. Like this example, to filter out those years B10 (47.8) plus or minus B12 (3.339).
AB
1YEARTEMP
21870 44.78
3187143.33
4187240.99
5187339.43
6187440.94
7187537.33
8187642.23
9187745.68
10187948.18
11
12Stdev3.339
I need to filter out data which displays only a few categories of information at the same time in the same column. For example, column A: David, Susan and William. After filtering the data, it will show David, Susan and william information. I have tried custom filter, but it only allows 2 conditions. I need to show more than 2 conditions of data. I have tried working out using office assistant guide on Multiple Criteria in one column to no avail. Only shows a single cell.
View 9 Replies View RelatedI bring in data with these same columns A thru J. I do not know how many rows will be comming in. I have create a macro that uses the advanced filter. I have a criteria range set up on sheet 2 with 6 variables, the main data is on sheet 1. This works fine if I use all 6 variables. I wish I could select from a list box which of the 6 or all variables I need(I might select 2,3 or more variables), & have that information populate the criteria range. I only filter in place(no copying), there is only filtering on column B, the label for the criteria range will always be "Name" it will be located on sheet 2 D:1. Is this possible or should I be trying some other function? Here is the macro that uses all 6 criteria. I have searched and could not find what I was looking for.
'add criteria range
Sheets("Sheet2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("D2").Select
ActiveCell.FormulaR1C1 = "e11*"
Range("D3").Select
ActiveCell.FormulaR1C1 = "e12*"
Range("D4").Select.........
I have a folder which has 200 files. I have extracted data from these files based on autofilter criteria. But there are many duplicate records extracted for the criteria. I need only unique records . Below are the codes. Where to I add the criteria for search records:
Sub ExampleSearch()
'Note: This example use the function LastRow
Dim basebook As Workbook
Dim mybook As Workbook
Dim rng As Range
Dim rnum As Long
Dim mnum As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
I recorded a macro to perform an Advanced Filter. I then adapted the range to & LastRow. My question is, can this now be adapted to remove the Select so the sheets are not selected when this is run.
Sheets("CIT Results").Select
Sheets("Open Calls").Range("A1:I" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Open Calls").Range("N5:V8"), CopyToRange:=Range("Q50"), Unique:=False
Moderators, can you please edit the Thread title. It should be "Advance Filter From and To Non Active Sheet"
Is it possible to use an advance filter to detect blank cells? Or can the criteria be set to detect all cells that don't contain a letter e.g. "Y"?
View 3 Replies View RelatedHow do you remove entries from a column such that only unique entires are left?
Say the spreadsheet contains only a single column:
david
kris
tony
hayley
kristy
john
david
test
hello
david
I tried an "advanced filter" with "unique records only" checked. It gets rid of one david, but not 2 (there are originally 3 of them, and we want to leave 1)
This is the result.
david
kris
tony
hayley
kristy
john
david
test
hello
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?
attached is the start of the assesment question.
You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.
Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
Whenever she clicks on a cell, the cell to the right of it is also selected. When she tries using tab to move to a new cell, she can only move between the two selected cells. Same with using the enter key. As such, it is extremely difficult for her to modify only one cell, since she always has two selected.
The F8 key, as well as Ctrl+F8. However, pressing the F8 key only adds more cells to the autoselection, and Ctrl+F8 allows her to select one cell, but also highlights the cells around it, and when we tried to select other cells, every cell we clicked stayed highlighted.
Additionally, sometimes when she clicks a cell, it will just select that one cell. Click it again, and the problem is back. I haven't been able to determine any patterns to this behavior, and I know there is no problem with the input (the keyboard and mouse are standard-issue in our lab, and we keep them well maintained).
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
Attached File : samplev1.xlsx‎
In my excel workbook, I have a customer table and invoice sheet among many others.
As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.
Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.
For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes
Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:
Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No
I have used the following =IF(ISBLANK(Q6),"",(NOW())) & =IF(ISBLANK(Q6),"",(Today())),
but when the excel file is closed and opened the date changes to now or today. How can I make the date stay and not change?
I am trying to make a date auto populate when an entry is made into a specific cell.
Context:
Cell H has a drop down list of staff names who will be assigned to a project, once a name is selected from the drop down box:
Cell I needs to auto-display that days date
is this possible with just a simple formula?
2 Different worksheets to work with
The "Nursery" Worksheet
I already have code that puts the Auto-Sum amount below the last data row in the column named "Nursery Grand Total" in the Nursery Worksheet.
This Auto-Sum amount, however, will always be in a different row because the amount of rows generated from the report is always different as well, therefore, the Auto-Sum cell/row changes with that to be right below the last data row in the "Nursery Grand Total" Column.
I would like to copy (values only) the amount from this dynamically changing Auto-Sum cell and paste it into another worksheet named "Totals".
The "Totals" Worksheet
In my "Totals" worksheet, I have two columns.
"Master Total Description" and "Master Grand Totals".
In the "Master Total Description" column, I have a cell named "Nursery Grand Total" which is exactly the same name as the header row in the "Nursery" worksheet.
So,
In the "Nursery" worksheet/"Nursery Grand Total" column, I would like to copy the auto-sum amount
and paste it into....
the "Totals" worksheet/"Nursery Grand Total" row/"Master Grand Totals" column
Here are some pictures for reference...
I have the following code:
[Code] ....
When I run the macro, some columns are already hidden. The macro doesn't seem to autosize cells correctly. For instance, one cell in a row appears to have some contents hidden (or below the reading area of the cell). In other instances, the rows are auto-size to huge heights and widths.
I would like to auto populate a cell (A2) due to the input results from another (A1).
For example, if I enter A1 with "100%" then I want A2 to auto populate with "Complete". And just the same for other percentages; if A1 was input with any range from 1% to 99% it would populate A2 with "In Progress". And if A1 was to equal 0% then A2 would be "Not Started".
I'm having a difficult time making this work properly.
To simplify, I want:
cell A1 to fill with blue when cell G1=0 or greater.
cell A1 to fill with yellow when G1<0
Next step is to apply this to cells A1:A100 based upon cells G1:G100.