In sheet1, (B2 & C2) are the numbers entered by the user. After they entered, it will create rows of table according to the number of groups they entered on the respective sheets.
how do I let it create the groups itself?
I have data in a column (G) consisting of zero and ones. I would like to count the number of clusters of the number 1 in the data. For example in the data below there are 8 ones. But instead, I need to calculate how many groups of 1s occur. So in the case below the group of 1s = 3.
In terms of what defines a group. Whenever there is a zero either before or after the occurance of a 1 constitues a group, i.e the groups are broken out by zeros.
0
0
0
0
1
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
1
1
1
1
0
0
0
0
0
0
1
0
0
I would like to be able to enter a number in cell a1. Let's say 44.2. I want that number to actually be locked in as 100'-0" in cell b1. Below in the next row I want to enter another number, lets say that one is 41.5 into cell a2. And then I want the cell b2 to return the difference in feet and inches. So ultimately the only cells that I will entering data into is in column a and column b would just return information.
The other thing I am trying to figure out is a formula for converting decimal feet to feet and inches (which I have found) but a formula that is less than 40 characters.
I have two groups of data? I selected the cells in yellow and defined the name of those cells as group1. I want to be to do absolute value of individual cells and add them up. I tried pressing shift control and enter and it still isn't giving me a number. I can do it if the numbers are next to each other but if they are separated by a row it won't let me.
[URL]
I need to create a grouped sequence that changes every 10 rows.
So in Column A cell A2 the number would start at 01349 and every 10 cells it would decrease by 1 and go on down to 0
so it would look something like this:
01349
01349
01349
01349
01349
01349
01349
01349
01349
01349
01348
01348 ...
i need code that can look at cell d23 and take that number( say it is 13) and break it down into groups. that have 3, 4 or 5 per group. in this example 13 can be broken down into
View 3 Replies View RelatedI am trying to add 2 groups of cells and then divide the number by 60?
I can't get it to divide? It will only total.
=SUM(T807:T832)+SUM(T837:T846)/60
1) I need to transpose about 2000 rows of 1 to 7 columns (A to G) of text/letter combinations of 1 to 5 characters into one column, each row under a group name (groups have one to three rows, so there's about 900 groups). The group name is in a row by itself, members are in the 1, 2, or 3 rows right underneath it. 2) Then I need to number the groups. There are no absolute common letter or number combinations within a group, so I can't use the first, mid, or last 2 or 3 characters to signify a common group. I do know how to use the copy and transpose feature, but would like to automate, if possible since there's so many. And automate the numbering task as well.
An example of the data is:
ABEGF
AMMBG AMNBG ANBG1 ANBG2 BEFGL BEFG1 BEFG2
FG1 FG2 GH1 GH2 GH3
I am trying to find a way to sort a string of numbers within a cell in numerical order as they are entered,regardless in what order they are entered. Example 123,45,478,12 entered cell, and want it to sort in numerical order as it gets entered in the cell...12,45,123,478,4564
View 2 Replies View RelatedI need a VBA function to extract number sequences from a string and separate them with hyphens In the example below cell A1 has the value 'xx2 yyy34 zz515' The code must produce the value '2-34-515' from the above example I have the following function that extracts the numbers but need a way to separate the groups with a hyphen
Code:
Function parseNum(strSearch As String) As String
Dim i As Integer, tempVal As String
For i = 1 To Len(strSearch)
[Code]....
Im trying to enter an employees name into a data list. I have a program that generates a list including their employee number into column B. I want to write something to put the employee name into column C. Im working with 60+ employees so i dont think if statements will work? anyway to reference a chart or table with the numbers and names on it?
View 9 Replies View Relatedif I use the following formulae in A1 and fill it down, I get the serialization 1,2,3....etc. =(INT((ROW()-1)/1)+1)*1
But if I begin formulae at A11 and fill down, I get 11,12,13.....etc. Now I type 1 in A11, how do I modify the formula so that A12 will be 2, A13 will be 3, A14 will be 4 and so on ?
I am entering values in columns AV and BD of the attached spreadsheet. Rather than manually editing each value to adopt the decimal place value of the adjacent cell is there a formula that can achieve this before the value is entered? The decimal place values are determined using a VLOOKUP table (column3) on the NES tab.
View 2 Replies View RelatedIs there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
View 8 Replies View RelatedI am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.
1
1
2
3
4
[code].....
I have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
I am trying to simply count the number of times each entered name appears on my list IE if John Smith appears 3 times in one sheet, in a column after his name would simply be the number 3. I tried this doing =COUNTIF(A8,A:A) Where A8 is his name and column A is all names. I keep a return value of 0 every time!!!!! I even tried =COUNTIF(A7,A12) where they were both the same names. And yes,I did do Ctrl + Shift - enter
View 2 Replies View Relatedtrying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?
My colleague in accounts has a sheet that lists all the invoices and values and other 'accounts department things' that I know little about.
Anyway, she has to manually input all the invoices she receives onto this excel document but sometime receives duplicate invoices (& inputs them twice be accident).
Is it possible for someone with macro skills to create a macro to run in a worksheet so that if she enters a number in a given column more than once an alert box appears to tell her. I've summarised the 'rules' below*
*Can is be written so that I can edit which column the alert is based on?
[In (for example) column F is a list of invoice numbers - all unique]
If user types a number in column F that matches a number that already exists, show alert box "THERE APPEARS TO BE A DUPLICATE ENTRY IN COLUMN F"
====
Any further clarification, let me know i'll try to post a blank sheet if my boss lets me.
Example;
Cell 1 equals a number
Cell 2 is the number entered into cell one times(X) 35 up until the number 6 is entered. Any number above 6 is Times 50. So. 1-6 is times(X) 35 while 6+ is Times(X) 50.
I am. Here's another example:
A salesperson gets a commission of $35 for each widget sold up to the first 6 widgets. If he sells more than six widgets, he gets paid $35 for the first 6 and $50 for anthing over 6.
The sales person will enter the number of widgets sold into a cell and the formula should do the rest.
I have been stuck on this for months.
I've done lots of looking on the forum, and the helpfiles, but just cant seem to format my text box properly! Its a form where the user inputs to the text box. I want to control thatway where they enter numbers e.g 05052008 and it gets converted to 05/05/2008 or 05-05-2008. I've tried a range of things, but am really stumped!
View 2 Replies View RelatedI created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
I have Two Sheets in same workbokk.
Sheet1 Sheet2
Sheet1 B2 = "any numeric value (whole digit i.e 10 or 55 like) - user input "
Sheet2 Starting point is cell B3.
What need to happen - As soon as user enter number in Sheet1 B2 = any value say, 20 then,
Sheet2.Select
Range("B3").select
suppose i assign value p as,
dim p
sheets("sheet1").select
p = range("B3).value
now here i want to indetify this number, i.e.- 20 (User input) and then as soon as user enter this value,
step 1 - sheet2 get select.
step 2 - from B3 sheet2, by row wise it select next 20 row as well next 20 columns.
for eg.- from B3, the data should select like following,
B3 to U22.
and this selected area highlight with single border only. That's it.Therefore, my main problem is how excel will understand this numeric number ?
I am trying to use VLOOKUP to auto-populate a description from an entered part number. After checking up on how to do this in several different places I applied this formula to the relevant cell but all that it returns is #N/A.
I am very confused as all seems to be correct, but I am new to this and I am sure I am missing something silly. :P
On entering a part number into cell C13 on sheet 'Stores Receipt' it should search and find that number in column A on sheet 'Product List', it should then return the adjacent description from column B on sheet 'Product List' and show this in cell C17 on sheet 'Stores Receipt'.... Sounds simple hey! :D
The formula used is:
Format Random Number Cells As Draw Result Entered ...
View 9 Replies View RelatedI used the following guide to construct a hyperlink formula which takes me to the coresponding part number entered into a box,
http://www.ozgrid.com/Excel/hyperlink-lookup.htm
It works well in the database I set up with all the part number the problem comes when I tried to use the same hyperlink formula in a diffrent work book referencing the database.
I get a correct returned line reffrence from the macro but it will not open a link to the work book
=HYPERLINK('Database Final.xls'!wbsheet & ADDRESS(MATCH(N16,'[Database Final.xls]DATABASE'!$A$1:$A$9999,0),1),N16 & "s' Info")
I need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached.
View 6 Replies View RelatedI have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.
Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then
[Code]....