We have list for entries that is shared among few people. In that list, we manually add number for the entry. So if I input 99 then, next person should add 100 and so on. I would like to automize this, so say If I use this, then no one else can use it in the mean time and then when number is input, it should disallow if same number is input and it should automatically enter the next number.
View 3 Replies
I'm am trying to find some way to determine if a particular tab falls before/after/between other tabs in a workbook. For example, if I have a tab "Top" and a tab "Bottom", I want to be able to determine if tab X is between them.
View 14 Replies View RelatedI am trying to determine if it's possible to control the order of like events. In other words, if I have set up two Worksheet_Change events, can I control which one fires first? Or, as a second best, can I determine which one will fire first?
I am using Office XP on Windows XP, but I'm looking for a generic solution if possible.
I'm trying to determine if the last character of a cell is a number or not a number. So in a cell I may have a string like:
ZXC123
or
ZXC123A
If the last character is not a number I want the letter returned. So I thought I could use a formula like: =IF(NOT(ISNUMBER(RIGHT(D88,1))), RIGHT(D88,1),""). However for the string ZXC123 it still returns '3' so anyone have any ideas how I can do this?
I have a column of random number in Column A and need a formula to identify if any of the numbers in Col A is a prime number. If it is, then say "Prime" in the cell adjacent to the prime number in Col B. Eg if A12 has a value 17, then cell B12 should say "Prime".
Determine If A Number Is Even Or Odd. i would like to Determine If A Number Is Even Or Odd...?
View 3 Replies View RelatedIs it possible to programmatically determine the page number on which a specific cell will print?
View 14 Replies View RelatedTrying to do a simple workbook here, with hopefully no macros/VB script.
Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.
The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.
The cell I'm trying to create is part of a 'stats' section.
I have a worksheet that contains active rows and hidden, inactive rows. On occasion, I would like to unhide an inactive row. I intend to do this by running a macro which will generate an input box to enter the file number of the row in question. I can restore the row, but what I am unable to find a solution for is converting the file number to the row number to be restored. The file numbers reside in column A, so if I input file number 2300, I need the macro to look through column A, find the cell that contains the value 2300, and return (as a string?) the row number that contains that value. From this I can have that row returned to visible status.
View 9 Replies View RelatedI have a report of time balances that employees enter. I need to run a macro that looks at each cell and determines if the number is divisible by 4. (We only track vacation and ill time in 4 hour increments) If the number the employee entered is not evenly divisible by 4, I want the cell highlighted.
All of this I have figured out, the loop to look at each row, the IsNumeric function to test if there is a number...
My problem is with the MOD. I figure I should be testing number MOD number, but it is not reliable.
4.1 Mod 4 returns 0
.5 Mod 4 returns 0
I just need it to return zero if the number is evenly divisible by 4.
I considered the RoundUp function, but then it would also round anything above 3 hrs to 4, and that won't work.
I have client sheets that need to be printed at the end of each month. Sometimes I need 1 sheet, sometimes more. I have a macro which pops up a userform in which I can select the client I want to print and the month. I would like to be able to have the macro determine how many pages need to be printed so I get all the pages and do not end up with any blank pages. Is this possible?
View 1 Replies View RelatedIt's been awhile since I've done this...and I tried searching for the solution but to no avail.
I'm trying to edit the contents of a series of numbers by using the Rounding function. When I execute my macro on the next cell, it uses the value of the original cell when the macro was created. Here's a copy of my macro:
Sub RoundTest()
'
' RoundTest Macro
' Macro recorded 7/26/2007 by Aramark Uniform Services
'
' Keyboard Shortcut: Ctrl+l
'
ActiveCell.FormulaR1C1 = "=ROUND(2103.86,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"
Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.
I have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:
A1 B1 C1
0 0.1 TRUE
0.2 0.5 FALSE
0.6 0.8 FALSE
0.9 1.2 TRUE
1.3 1.6 FALSE
1.7 2.0 TRUE
is it possible if I have a directory like the following
[Code].....
with .csv files which are placed in arithmetic order 01,02,03 etc to determine if one .csv file is missing and print an error that this file is missing? I think that if I knew how many files I got I would create an array and check one by one the files.. But in case I have a dynamic directory and the number of files is not the same all the time , is this possible?
if the active cell is formatted as number and I write
x = Cells(Row, Column).Value
and the cell is empty or blank, x will be set to 0.
How can I determine that the cell is blank and not actually 0 ?
Is there a formula I can use to determine the number of work days in each week for a given month. The work week would start Sunday, with Friday and Saturday as days off. Each week would be in their own row.
i.e.
January
Week 1: 2 days
Week 2: 5 days
Week 3: 5 days
Week 4: 5 days
Week 5: 3 days
[Code] ..........
How can I interrogate the contents of a cell to determine whether or not it contains a number formatted as text? I intend to run a .value = .value over each such cell to convert it to a number format, but I only want to do this to relevant cells.
View 9 Replies View RelatedHow can we determine Number of Logical Pixels per Inch based on the system configuration using API and return the value to Cell A1 of Sheet1.
I'm trying identify the active cell row number in a current region for viRows below. Currently I'm getting the active cell row number on the total worksheet.
With ActiveCell.CurrentRegion
viCols = .Columns.Count
viRows = .Range(ActiveCell.Row).Count
End With
DETERMINING ROW NUMBER FOR DATA FOUND WITH VLOOKUP IN VBA
I have a user form with a combobox (called STKCODE) that calls up a vlookup routine to search for this item in a named range on a worksheet and retrieve related data to display on the userform. All this works fine.
What I would like to know is how to easily determine the row number on the worksheet, representing the row the data is stored in, if thats at all possible....
I am trying to add a number to invoices (invoice numbers) on a UserForm that is used to make and/or modify scheduled service records for a cleaning business. We fill out the schedule well in advance of when the jobs are scheduled to be done, some customers are on a set schedule such as once a week, everyother week or once a month. Some just call in when they need cleaning. Each day has 27 rows set aside for possible customers (most are not used). We make out as many invoices in advance as is possible and fill-in the rest as they call-in. This leaves a lot of gaps between days
I have an auto-advancing formula tha takes a helper column to give me invouce numbers when there is a customer listed in column B and blanks space where there is no customer listed. This does make for volitile invoice numbers.
Formula in row 3 of the invoice coulmn "A": =IF(B3="","",H3)
Formula in row 3 of helper column "H": =IF(B3="",H2,H2+1)
The helper column only advances if there is a customer listed in column B of that row otherwise it repeates the last number. The invoice column only displays this helper number if there is a customer listed in column B of that row. I can't figure out how to get this to work on a UseerForm with vba.
This is probably a simple question, but I couldn't find the answer by searching...
I have a macro that I wish to run which calls one of two different macro's depending on if the user has Excel version 2007 or if they have an earlier version.
This is to overcome 'Save as PDF', which only works in Excel 2007.
I'm creating an order form in excel. I would like to add a control there where when it's triggered, excel would automatically assign a number that hasn't been used before and save the file using that number. It's like an order number generator.
For example, let's assume I'm entering a purchase order in excel. After I'm done doing that, I click on the "SAVE" control button. This will assign the number following the last saved order and save the file in a location I specify in this format: filename POnumber.
I have a list of unique number. They are part numbers. For convenience for the user, they cannot be resorted. A list may look like this:
AAAA
BBBB
AAAA
AAAA
CCCC
(blank cell)
CCCC
What I would like is a formula that i can put in the adjacent cell that will result in the following:
AAAA 1
BBBB 1
AAAA 2
AAAA 3
CCCC 1
(blank cell)
CCCC 2
Essentially, the formula/solution will "know" that the third occurence in the list is actually the 3rd occurence, and so forth... I tried COUNTIF but that just gave me the total number of occurences
AAAA 3 wrong
BBBB 1
AAAA 3 wrong
AAAA 3
CCCC 2 wrong
(blank cell)
CCCC 2
I have 1500+customer names in column C7 of table. I'm looking for a SUMPRODUCT formula that achieves the following 2 logical goals. First, it needs to determine the number of unique names in column C7. It can't just count the rows because many customers appear multiple times in the range. So, I have been using the following formula and it works great:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&"")))
Second, I'd like the formula to tell me how many of the unique names in C7 correspond to public companies. Column C8 of my table indicates whether or not a company is public ("Yes") or private ("No"). So, I've been using the following formula and it seems to deliver the right result:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&""))
*
(COUNTIFS(Table[C7],Table[C7]&"",Table[C8],"Yes")>=1))
But the added COUNTIFS statement seems very unwieldy and indirect to me. Why do I have to examine every instance of a particular company's name when the answer could be determined by looking at any instance of the name -- after all, each company in C7 is either public or private, and has the same corresponding value in C8 (whether "Yes" or "No") everywhere that its name appears in C7!
So that's my first question: is there a clearer, more efficient way to achieve the same result? My second question has to do with toggling the public company test.
What if I create a special cell, say A1, that will always contain either the word "On" or "Off"........
I'm trying to copy the content of a word document (File A) to another (File B) using Excel VBA. File A has about 100's of pages (not sure of the number as it varies) and this needs to be split to different files, each having 15 Pages.
Below is my code, where I'm able to select the content of the first Page and paste it in the target folder, but not sure how to determine the number of pages in word using excel VBA.
Note: Copying the content should be done, page wise only.
[Code] .....
I have an upholstery business and I have created a workbook containing multiple sheets (proposal, invoice, sales order log, etc.. ) Following, I have saved it in Template format, which I open and save by order number somewhere else. The issue is, I don't know how to track what the last order number I used was....
Is there anything I can work using excel that will allow me to make that easier without having to go open a window and looking at the last order number after arranging the workbooks by name inside a folder?
I'd like to take the value from one cel, and reverse the digit order in another cell. Example cell c11 value is 1234, I want to display that value in cell c12 as 4321. Is there a way to do this?
View 9 Replies View RelatedI am trying to number rows based on criteria. when the value changes add a 1. This is what I need to have happen
Apple - 1
Apple - 1
Apple - 1
Orange - 2
Orange - 2
Plum - 3
Plum - 3
I can get an if statement to number everything 1.
