Automatic & Dynamic Row Numbering For Outline Numbers Like #.#.#

Mar 11, 2008

I would like to ask for some assistance to my Spreadsheet problem.

To illustrate my problem please see below:

1------------> Level 1
1.1----------> Level 1 Sublevel 1
1.1.1--------> Level 1 Sublevel 1 Item 1
1.1.2--------> Level 1 Sublevel 1 Item 2
1.2----------> Level 1 Sublevel 2
1.2.1--------> Level 1 Sublevel 2 Item 1
1.2.2
2
2.1
2.1.1
2.1.1
2.2
and so on

I need to have a formula to automate the adding of outline numbers from "Level 1, Sublevel 1, Item 1..." So that if I deleted or added a row, the rest will automatically update themselves.

I will be the one manually inputing the Top Level and Sublevels I just need that the Items will be automatically added and computed basing from the previous level.

What I am planning to do is:
1. Check previous cell if the format has two decimal points or periods --> kinda stuck here

2. If there's only one then it will be the Sublevel 1 Item 1 (or depending to its location)

3. If there's already two periods "." then it will compute automatically for what item it is already.
ie:

1
1.1
1.1.1
1.1.2----->Checks the previous cell if it has 2 decimal places then if it does then it will add 1

View 9 Replies


ADVERTISEMENT

Outline And Rollup Subtotals Based On Outline Numbering In Column A

Jul 10, 2012

I have a spreadsheet that contains an outline-like number in column A, except where there are values in Column C. What I want is to automatically outline based on column A and subtotal at each succesive outline level (i.e. I would like to be able to roll up the subtotals to variousl depths of the outline).

Code:
A B C
1.1first level
1.1.1Second level
1.1.1.1third level
value 5
1.2first level
1.2.1second level
value 7
value 4
1.2.2second level
1.2.2.1third level
value 6

View 2 Replies View Related

Automatic Numbering In VBA Etc

Dec 18, 2007

I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...

1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.

2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?

3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?

View 12 Replies View Related

Automatic Sheet Numbering

Nov 5, 2008

I have a report blank that is comprised of numerous excel worksheets (fixed letter size). During the completion of the report, one may add, delete, and/or move worksheets. I want each worksheet to have a cell that dispalys 'page # of total number of sheets'. Is there a way to automatically update this information?

View 4 Replies View Related

Automatic Numbering Based On Last Number

Apr 16, 2009

I have got 2 sheets:

- list (list of transactions)
- accepted (accepted transactions).

I want to make macro, which will:
1. select max value from sheet.accepted "B:B"
2. enter max value +1 in sheet.list "B8"
3. automatic fill down from B9 to the end of list with number increasing by 1 (in B9=B8+1, in B10=B9+1 etc.)

I tried with this:

View 5 Replies View Related

Macro To Have A List With Automatic Numbering

May 8, 2013

The context of my question is "Invoice Numbering".

I would like to have a sheet where I can have an incremental number. Every time I run the macro it should strikeout the last number and it provides me an incremental number.

I used such a sheet in a previous job and I cannot find it online.

View 2 Replies View Related

Automatic Unique Numbering In MsgBox

Jan 2, 2008

After entering some values in a form I want to get a unique id for that record to be displayed in a message box and thereafter inserted into column A in the same row as the rest of the information. The numbering should be 1, 2, 3 etc and when I use the application the next time the id displayed should be the next number. This is what I've got so far, but now my unique id is always 1 (not very unique at all).

Private Sub UserForm1_Initialize()
Dim rIds As Range
Dim MaxId As Long

Set rIds = .Range(Cells(A, A), Cells(Rows.Count, 1).End(xlUp))
MaxId = Application.WorksheetFunction.Max(rIds)
With Me.IdBox.Value = MaxId

Private Sub DateBox_Change()
DateBox = Format(Date, "yy/mm/dd")
End Sub

View 9 Replies View Related

Automatic Next Cell Numbering For Bespoke Number Style?

Jun 15, 2012

I am trying to work out how to automaticaly fill in the next number in a sequence of bespoke numbers like this in column B 1012-0153-70, next 1012-0153-71, next 1012-0153-72 and so on so when i click on the next open cell in column B it adds the next number in the sequence

I have the following VBA code in my Sheet:

Private Sub Worksheet_SelectionChang()
With Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
If Target.Address = .Address Then
.Offset(-1, 0).AutoFill Destination:=.Offset(-1, 0).Resize(2, 1), Type:=xlFillDefault
End If
End With

but nothing seems to work. i also have the following VBA code on the same sheet:

Private Sub ComboBox1_Change()
End Sub
Private Sub Calendar1_Click()

[Code].....

View 3 Replies View Related

Numbering List Based On Dynamic Number?

Jan 1, 2013

I have dynamic number in K2. How to make numbering list whose length is based on that K2 number? I want that list to start from A2. So, in other words, if K2=3, the list should be like this:

A
empty (cause my header will be there)
1.
2.
3.

Notice that there should be period after each number too.

View 3 Replies View Related

NOT All Numbers Affected By Numbering Formatting

Jan 30, 2014

I'm viewing some financial data that needs formatting. I select the appropriate cells and format the corresponding values accordingly:

Right click / Format Cells / Number -> Decimal places:2 -> (check) Use 1000 Separator(,)

It's easier to read the sums and averages (at the bottom-right of the excel window) when the above described formatting is used.

But is it possible, and easily do-able, to have the zeroes show as 0 and not 0.00?

View 8 Replies View Related

Automatic Dynamic Sort

Jan 11, 2008

I have a spreadsheet that ranks the 500 components of the S&p 500 on an ongoing intraday basis. I would like to have the sheet sorted automatically as it is updated. I have very limited VB skills. Also, I heard there is a feature on excel 2007 that performs this function.

View 7 Replies View Related

Automatic Or Dynamic Rolling Month?

Jul 31, 2014

I would like to set the automatic rolling month when i select month at dropdown list and this will show at C5 and need to cell at the right hand side auto change month to Jul, Aug, ... Dec, Jan, Feb.

and at left handside show the previous month.

Rolling month.xlsx

View 2 Replies View Related

Automatic Custom Filter On Dynamic Data

Nov 27, 2008

I need a function/macro that will find all rows that have a specified value in column A and extract selected columns to a new spreadsheet. More, I need it to do it for every value in column A.

I would also like it to skip creation of new worksheet if value in selected row and column is null.*

I've been trying to combat this problem with advanced filters, which helped, but due to size of the data and range of values in column A it takes an entire day to process manually. Because the data is exported to another program after it's processed, it can't stay in the same sheet, also, linking back to the original sheet doesn't work because the data changes all the time.

View 4 Replies View Related

Automatic Updating Of Numbers In Formula?

Jul 29, 2014

I thought I could update formulas dynamically by dragging into new cells but it's not working.

Problem: I have a time series of input from a device that samples at 40Hz. The output I get from this device in Excel consists of 40 columns in row 1 (representing the first second) and then it creates a new row - row 2 - which also consists of 40 columns of values (representing second 2) - and it does this until the end of the response period which for me is 10 seconds. When I do a quick filter I end up with 10 rows, each consisting of 40 columns of data and all of this represents 1 trial. This then repeats for 32 trials.

I want to have all of my data for each trial in the same row. So I want the first 10 rows essentially collapsed into 1 row so instead of a 10x40 matrix representing one trial I have a 1x400 matrix representing that trial.

It starts out well enough - I make a row for my first trial and, if trial 1 second 1 = H2:AV2 and I'm typing in cell AY2, I just write =H2 in cell AY2 and drag across for 40 cells until I get to CK2 which will have =AV2 in it because of the automatic updating from dragging. Then I move one cell over - to CL2 - and type in =H3 and start the whole process again until I have all my 400 values in one row. I know this is a tedious way to do this but I figured once I did this it would be a simple matter of formulas and dragging to fill in the rest.

Not so. Is there a way to dynamically update references? So for example, cell AY2 has the formula =H2 in it. Now I KNOW that in AY3 I want to have the formula =H12 (because the beginning of the next trial is 10 rows down from H2) and I know I want AY4 to have =H22 etc. but when I drag the reference to H2 down it just changes it to H3, which makes sense but having a formula like =AY2+10 returns the value in AY2+10 instead of the reference, which again makes sense but I'm totally blanking on how else to do this. I've tried using offset and indirect and offset, for example, will work if I hard code in the numbers (e.g. =offset(H2, 10, 0)) but if I drag this formula down neither the 10 nor the 0 changes so I get the same formula in every which is obviously not what I want (and I guess if it did change, it would just change the 10 to 11 anyway, which again, is not the increment I'm looking for). I've also played around with adding constants of 10 and got nowhere, probably because I'm doing it wrong because I'm fairly sure I'll have to add a constant of 10 somewhere.

View 5 Replies View Related

Outline/wbs Sort

Jun 25, 2007

have an Excel VBA function to sort an Outline/WBS?

I have tried the conventional Excel text and numeric sort but they do not work on an Outline/WBS.

I have a column in an excel workbook with a Outline/WBS.
If a user uses the default Excel sort I need to sort the Outline/WBS back to the correct Outline structure (example below):

1
1.1
1.2
1.3
1.3.1
1.3.2
2
2.1
2.2
2.2.1
2.2.2
2.3

View 9 Replies View Related

Dynamic Sum Range- The Last Numbers

Aug 3, 2006

Can't seem to find a way to create these sum ranges. In column B, I want to sum the last 4 numbers in column A.
....Unfortunately, there are many (varying) blank cells in column A.
....Here is an idea of what I'm going for:

For cell B19
=SUM(OFFSET(B19,0,0,-COUNT(B3:B100)=4))

....This sum will be filled down. I want to sum the last 4 numbers, regardless of the length of this range.

View 9 Replies View Related

Selected Cell Outline

Oct 9, 2008

The outline that indicates my selected cell is a very pale blue which I can hardly see. how to change it to black or some color I can see?

View 3 Replies View Related

Outline.ShowLevels Event And Value

Jun 12, 2006

1. Is there an event when Outline.ShowLevels are changed? I want to hide some columns when the outline level changes depending on the level. For example if Outline.ShowLevels is 2 I want to hide certain columns and if Outline.ShowLevels is 3 I want to unhide those columns. I realise you can have a mix of levels showing but I will hideunhide the columns based on what has changed ie from level 2 to level 3 or vice versa.

View 6 Replies View Related

Group And Outline Columns

May 25, 2007

i have seen this cool feature in a workbook, but I am unable to find out how implement it myself. I can't really explain it (thus I am unable to find good search results) so I have added a little picture of what I mean. Its about adding controls for hiding and unhiding columns. How do I get such nice plusses and minusses into my workbook? I use Excel 2002

View 4 Replies View Related

Data Validation For Dynamic Numbers

Dec 2, 2006

I am looking for a solution for my problem: I work for a bus company, I need a spreadsheet where I will record the bus numbers into a sheet but I only want to be able to add the number once ie. error if duplicate bus entered. Also the numbers will change from time to time, old buses sold when new ones arrive. I have the following code that someone helped me with wich works fine provided you don't have to change the numbers.

=And(Or(And(E42>=121,E42<=125),And(E42>=149,E42<=156),And(E42>=320,E42<=363),And(E42>=700,E42<=799),And(E42>=800,E42<=991)), COUNTIF($B$3:$E$46,E42)<2)

View 2 Replies View Related

Excel 2003 :: Group And Outline

May 14, 2013

In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).

If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."

If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.

I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.

View 4 Replies View Related

Turn Off Group Box Outline And Label?

Sep 15, 2013

I know how to insert various groups of radio buttons on one sheet. They must be in their own group box control. The problem I have is that I don't see any option to turn off the resulting group box outline and label so it cannot be seen. Is there some way to do that in Excel 2010? I have 16 groups of two radio buttons across the top of the sheet and space is at a premium. If there was no outline it would look much nicer. The cells below the radio buttons function as the labels the way I am using them.

View 5 Replies View Related

Enable Outline But Protect Sheet

Sep 15, 2007

I have a sheet that is several hundred rows long and so I have grouped like data so the user can expand or colapse the data they would like to see. There is nothing on the page the user will need to modify so I have protected the page so they cannot make changes. If I protect the sheet then I am no longer able to expand or colapse the groupings.

View 6 Replies View Related

Trapping Outline Expand / Collapse

May 15, 2009

I suspect I need a class module for this...

How do I trap the event when a user chooses to expand / collapse outlines (columns in this instance) on a worksheet?

Basicallly, when attempting to expand I want to prompt the user to enter a password and then unprotect the sheet. And if collapsing to prompt again and then protect the sheet. I have this bit covered, just not sure how to trap the event.

View 9 Replies View Related

Group/Outline Worksheets When Protected

Aug 20, 2008

I Need the outline/group feature to work on a protected workbook with over 200 sheets, meaning a macro to cover the entire workbook not just a single sheet. This macro should travel with the workbook as it will be on many different peoples PC's

Private Sub Workbook_Open()
Dim cSheet As Integer
Dim tSheet As Worksheet
On Error Resume Next
cSheet = 1
Set tSheet = Worksheets(cSheet)
While Err.Number = 0
Call wbProtect(tSheet)
cSheet = cSheet + 1
Set tSheet = Worksheets(cSheet)
Wend
Err.Number = 0
End Sub..........................

View 2 Replies View Related

Group & Outline On Protected Worksheet

Jul 25, 2006

I curently using the group and outline to subtotal certain data. I do not want to data to be modified. As a result I protect the particular sheet. Problem is once I protect the worksheet, I can't expand the data hidden (using the group & outline). Is there anyway that i can expand the data & in the same time protect the
data. A sample in enclose(without worksheet protected)

View 6 Replies View Related

Show Different Outline Levels Of 2 Groups

Mar 19, 2008

I have two groups (outlines) of columns on the same sheet. the following line :

ActiveSheet.Outline.ShowLevels 0, 1

controls the two groups. What if I want different levels od detail for each group ?
TIA.
Daniel

View 2 Replies View Related

Capture Group/Outline Row Hide/Show

Apr 26, 2008

The following is how the data is displayed:

[JAN] [a] [FEB] [b] [MAR] [c] [Q1] [APR] [d] [MAY] [e] [JUN] [f] [Q2] etc.

Basically, it is months and quarter. However I have an additional column that is hidden after each month - [a], [b], [c] etc. Those are displayed whenever a cell in the previous column is clicked. Now, I select all the columns from [JAN] to [c] and group them and I have a + on the top most row.

Problem is when I click on +, it expands the month as well as [a] [b] [c]. I have a macro to hide these, but how do I trap the [+] [-] action and run the hiding macro after that?

View 5 Replies View Related

Dynamic Formulas- List Of Serial Numbers In A Column

May 26, 2006

i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' )

my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as:

' =counta(b1:namedrange1)

i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,

View 9 Replies View Related

Data Validation Dynamic Sequential Numbers List

Jul 21, 2007

I am looking for a way to define a 'Data Validated' Cell to hold a List of Dynamic Sequential Numbers - from 1 to the Value of cell A4. For example:

If cell A4 has a value of 5 the 'Data Validated' List will be 1,2,3,4,5

I have tried to 'name' the Array-Formula {=ROW(INDIRECT("1:"&A4))} and put the 'name' as the List source but without success.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved