Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I can do it although I use a helper column. It could all go in one cell but it gets way longer then a formula I would like to see</p> <p>If you have levels in column A you will calculate a decimal ID in column B and an actual ID in column C</p> <p>cell B2=1 (since the formula doesn't work on the first of the series)</p> <p>other cells in column B use the formula</p> <p><code>=INDIRECT("B"&amp;MAX(ROW($A$2:A2)*($A$2:A2&lt;=A3)))+INDEX({1,0.1,0.0001,0.000001,0.00000001,0.0000000001},MATCH(A3,{1,2,3,4,5,6},0))</code></p> <p>Entered with <code>Ctrl</code> + <code>Shift</code> + <code>Enter</code></p> <p>This formula finds the value of the decimal ID from the last row that has a level less then or equal to itself (indirect part)</p> <p>the index looks up the Level and adds the right amount to the value found in the first part of the formula.</p> <p>Column C uses the formula</p> <p><code>=B2*10^IF(B2=INT(B2),0,LEN(B2)-FIND(".",B2))</code></p> <p>which just rounds the number up to a integer by finding the number of decimal points (if statement) and then raising 10 to that power and multiplying by the decimal ID.</p> <p>If you really need to do it in one column you could paste the whole first column everywhere you see <code>B2</code> in the second formula but that would make the formula totally illegible.</p> <p><img src="https://i.stack.imgur.com/n1Y4D.gif" alt="enter image description here"></p> <p>Gordon</p> <p><strong>PART 2</strong></p> <p>As per the request for more levels I've had to use a different technique which deals mainly with text.</p> <p>Instead of using the arrays built right into the formulas I created a seperate table which makes it a little easier to handle, you could reference this with named ranges, a seperate chart or just put the numbers right in the formula if you want.</p> <p><img src="https://i.stack.imgur.com/9XEVU.gif" alt="enter image description here"></p> <p>For the cells you enter the Level and sigfigs.</p> <p>Digits kept is sigfigs from the level above minus 1 (<code>M3=L2-1</code>)</p> <p>Digit Add start is the sigfigs from level above (<code>N3=L2</code>)</p> <p>Digits add length = sigfigs from level - sigfigs from level above +1 (`O3=L3-L2+1)</p> <p>For the actual index formula the first row has the value 1 again and then in cell B3 use</p> <p><code>=LEFT(B2,INDEX($M$2:$M$12,MATCH(A3,$K$2:$K$12,0))) &amp; MID(B2&amp;"00000",INDEX($N$2:$N$12,MATCH(A3,$K$2:$K$12,0)),INDEX($O$2:$O$12,MATCH(A3,$K$2:$K$12,0)))+1</code></p> <p>To break this down</p> <p><code>=LEFT(B2,INDEX($M$2:$M$12,MATCH(A3,$K$2:$K$12,0)))</code> </p> <p>means keep the left part of the index above. Find the length to keep from the digits keep column</p> <p><code>MID(B2&amp;"00000",INDEX($N$2:$N$12,MATCH(A3,$K$2:$K$12,0)),INDEX($O$2:$O$12,MATCH(A3,$K$2:$K$12,0)))+1</code></p> <p>means add a bunch of zeros to the end so you can use a level higher then the current one. Find the digit you need to start with, and the number of digits you want to use. Add 1 to this value.</p> <p>The &amp; sign between them will concatenate them together.</p>
    singulars
    1. This table or related slice is empty.
    1. POExcel Generating a formula based sequence based on levels
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USgtwebb
    1. USgtwebb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POExcel Generating a formula based sequence based on levels
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI am not able to replicate your example .. How can I do it.. column B just returns 0
      singulars
      1. PO
      1. USuser1799524
    2. COare you putting the formula in with Ctrl+Shift+Enter? are the levels in column A? are the levels between 1 and 6?
      singulars
      1. PO
      1. USgtwebb
    3. COYes, and only 0 is returned, if possible can you upload the file so I can test if its work
      singulars
      1. PO
      1. USuser1799524
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload