Anyone know how to replicate this in an appsh...

[Kolbi_C]

Anyone know how to replicate this in an appsheet formula?

Text(COUNTIF($D$2:$D2,D2),“00#”)

It gives me the number of times DocID 3010 (or whatever DocID is returned from a set of criterion) is contained within or above this record’s row in my table. I’m trying to use this to create the true Key of doc ID 3010-001, then 3010-002, etc.

[Levent_KULACOGL]

Though your query is a little bit ambiguous and I haven’t understood how your formula returns that DocID, but you can use an expression like this:

IFS( COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))<10,CONCATENATE( “3010-00”,COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))), AND(COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))>=10,COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))<=99),CONCATENATE( “3010-0”,COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))), AND(COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))>=100,COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))<=999),CONCATENATE( “3010-”,COUNT(SELECT(TableName[ColumnName],CONTAINS(“DocID”,[LookupColumnName]))) )

This expression will eval to:

3010-001…3010-009 3010-010…3010-099 3010-100…3010-999

[Simon_Blackburn]

@Levent_KULACOGLU That’s one hell of an expression but oddly enough, I follow it.

[Levent_KULACOGL]

@Simon_Blackburn you can shorten the expression a bit, by placing COUNT(SELECT(…)) part of expression into a seperate Virtual Column. Then the expression will look like:

IFS( [VirtualColumn]<10,CONCATENATE( “3010-00”,[VirtualColumn]), AND([VirtualColumn]>=10,[VirtualColumn]<=99),CONCATENATE( “3010-0”,[VirtualColumn]), AND([VirtualColumn]>=100,[VirtualColumn]<=999),CONCATENATE( “3010-”,[VirtualColumn]) )

[Simon_Blackburn]

I’ve got a load of excel/Google sheet formulas I need to convert to appsheet expressions. I’m gonna spend the next month or two checking out this type of question to try and learn how.

[Kolbi_C]

Thank you! I will try this, although I have a hard time understanding how it is similar to the way the Countifs function works.

To explain, the DocID is determined by a Valid_if of the combination of two parameters chosen by the user (the [Division] and the [Doc Type]). Then I want a hyphen and then an accumulator that tells me how many times this combination has been chosen up to this record so that the increment continually increases each time the user selects a given combination. Basically, I get a document ID that has 2010-001 for the first time that combo is selected, then 2010-002, 2010-003, etc. This way each doc ID is unique but coded to be recognizable.

Finally, my biggest challenge is revisions… and I’m open for ideas. If I have a revision of 2010-002, all these formulas still act and cause 2010-002 rev 1 to become 2010-003 rev 1 instead of the desired 2010-002 rev 2… This would be the final challenge to get a self-accumulating and managed document numbering system.

Sincerely,

Kolbi Christianson (720) 980-0028 **@

[Kolbi_C]

How about this one? Any help with this?

=vlookup(B2,GiftPlan!$A$1:$B$7,2,false)-sumif(B:B,B2,L:L)

Sincerely,

Kolbi Christianson (720) 980-0028 **@

On Sat, May 26, 2018 at 7:40 PM, Kolbi C. <****@**> wrote:

Thank you! I will try this, although I have a hard time understanding how > it is similar to the way the Countifs function works. > > To explain, the DocID is determined by a Valid_if of the combination of > two parameters chosen by the user (the [Division] and the [Doc Type]). Then > I want a hyphen and then an accumulator that tells me how many times this > combination has been chosen up to this record so that the increment > continually increases each time the user selects a given combination. > Basically, I get a document ID that has 2010-001 for the first time that > combo is selected, then 2010-002, 2010-003, etc. This way each doc ID is > unique but coded to be recognizable. > > Finally, my biggest challenge is revisions… and I’m open for ideas. If I > have a revision of 2010-002, all these formulas still act and cause > 2010-002 rev 1 to become 2010-003 rev 1 instead of the desired 2010-002 rev > 2… This would be the final challenge to get a self-accumulating and > managed document numbering system. > > Sincerely, > > Kolbi Christianson > (720) 980-0028 > **@ >

[Kolbi_C]

What this returns is the value remaining of the difference between a budgeted amount (which is the VLOOKUP) and the amount spent against the categorical name (in B2). I have a budget for spending on gifts for people, in a GiftPlan table. And I want to know in live time how much of that budget I will have used by spending a dollar amount which is listed in row L of my spending spreadsheet (called [Subtotal Cost]). I tried a combination of a de-ref - sum(SELECT()), but it returned the TOTAL against the budget, instead of the subtotal according to the selected name against the budget.

Sincerely,

Kolbi Christianson (720) 980-0028 **@

On Mon, May 28, 2018 at 8:16 PM, Kolbi C. <****@**> wrote:

How about this one? Any help with this? > > =vlookup(B2,GiftPlan!$A$1:$B$7,2,false)-sumif(B:B,B2,L:L) > > Sincerely, > > Kolbi Christianson > (720) 980-0028 > @ > > On Sat, May 26, 2018 at 7:40 PM, Kolbi C. <**@**> > wrote: > >> Thank you! I will try this, although I have a hard time understanding how >> it is similar to the way the Countifs function works. >> >> To explain, the DocID is determined by a Valid_if of the combination of >> two parameters chosen by the user (the [Division] and the [Doc Type]). Then >> I want a hyphen and then an accumulator that tells me how many times this >> combination has been chosen up to this record so that the increment >> continually increases each time the user selects a given combination. >> Basically, I get a document ID that has 2010-001 for the first time that >> combo is selected, then 2010-002, 2010-003, etc. This way each doc ID is >> unique but coded to be recognizable. >> >> Finally, my biggest challenge is revisions… and I’m open for ideas. If >> I have a revision of 2010-002, all these formulas still act and cause >> 2010-002 rev 1 to become 2010-003 rev 1 instead of the desired 2010-002 rev >> 2… This would be the final challenge to get a self-accumulating and >> managed document numbering

[Kolbi_C]

It renders as vlookup(RC[-1],GiftPlan!R1C1:R7C2,2,false)-sumif(C[-1]:C[-1],RC[-1],C[9]:C[9]) in the spreadsheet formula, if that helps to decode it.

Sincerely,

Kolbi Christianson (720) 980-0028 **@

On Mon, May 28, 2018 at 8:21 PM, Kolbi C. <****@**> wrote:

What this returns is the value remaining of the difference between a > budgeted amount (which is the VLOOKUP) and the amount spent against the > categorical name (in B2). I have a budget for spending on gifts for people, > in a GiftPlan table. And I want to know in live time how much of that > budget I will have used by spending a dollar amount which is listed in row > L of my spending spreadsheet (called [Subtotal Cost]). I tried a > combination of a de-ref - sum(SELECT()), but it returned the TOTAL against > the budget, instead of the subtotal according to the selected name against > the budget. > > Sincerely, > > Kolbi Christianson > (720) 980-0028 > @ > > On Mon, May 28, 2018 at 8:16 PM, Kolbi C. <@**> > wrote: > >> How about this one? Any help with this? >> >> =vlookup(B2,GiftPlan!$A$1:$B$7,2,false)-sumif(B:B,B2,L:L) >> >> Sincerely, >> >> Kolbi Christianson >> (720) 980-0028 >> @ >> >> On Sat, May 26, 2018 at 7:40 PM, Kolbi C. <@**> >> wrote: >> >>> Thank you! I will try this, although I have a hard time understanding >>> how it is similar to the way the Countifs function works. >>> >>> To explain, the DocID is determined by a Valid_if of the combination of >>> two parameters chosen by the user (the [Division] and the [Doc Type]). Then >>> I want a hyphen and then an accumulator that tells me how many times this >>> combination has been chosen up to this record so that the increment >>> continually increases each time the user selects a given combination. >>> Basically, I get a document ID that has 2010-001 for the first time that >>> combo is selected, then 2010-002, 2010-003, etc. This way each doc ID is >>> unique but coded to be recognizable.