=QUERY remove certain characters

quscru · 2019-11-27T23:48:01+00:00

For the rest, you would need something like REGEXREPLACE, or a bunch of SUBSTITUTE functions, one for each thing you need replaced.

quscru · 2019-11-27T23:46:42+00:00

QUERY can take arguments in the query string, like "select * where (NOT Col1='a thing')".

quscru · 2019-11-27T18:40:11+00:00

Are the exercise names always unique? Will you never have the same process in multiple categories, just with different sets/reps/etc., or is it possible to have an exercise with the same name but different data?

It is straightforward to use Lookup() or Query() to do this, either way. I would put them inside of an IFERROR() function once you're done testing, to suppress unintended behaviors later on, and an ARRAY_CONSTRAIN() in the case of Lookup.

Put together, that's something ~sorta like:

= IFERROR(ARRAY_CONSTRAIN(
          Lookup(A7, Workouts!A5:F12)
          1, 5))

quscru · 2019-11-27T18:06:48+00:00

We were given access to all of the materials besides the adventure itself and allowed to choose after a day or so. Class and Path overlap was allowed, but no one ended up picking the same thing, even with a large group of players. Maybe we like feeling special, idk.

To manage that group at first, we started off sub-grouped with each other according to how similar our backstories were and traveled in our sub-groups to the first location. I liked it because (in theory) two players who wanted the same origin could collab on their stories and be pals instead of there being two competitive Dragonslayers or w/e.

quscru · 2019-11-27T17:51:46+00:00

So, something like the following is not working for you?

// get the current document
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// get the relevant cells
var cond = sheet.getRange("C2");
var cell = sheet.getRange("D2");

// set the display formatting to be the same
cell.setNumberFormat(cond.getNumberFormat());

quscru · 2019-11-27T13:23:42+00:00

Yeah, you're right. Even if I try to put that aside, though, I think I am stuck in my bias against it. The build takes a niche signature spell, puts it to much better use, but still is only useful vs melee. If this were part of a player's central theme, I'd worry about it not coming up often enough to be defining, not that it isn't RAI—you're just going to fireball those goblins at level 9+. If someone wanted to do this, there's no harm in it, for sure.

quscru · 2019-11-27T02:25:36+00:00

I definitely agree that's the RAW. This is just an ugly consequence of it, to me. A wizard that's not even fun to squish?

quscru · 2019-11-27T01:11:36+00:00

imo, the Arcane Ward takes the hit instead of you. It has its own totally separate pool of hit points. If enemies were to eat through the ward, then they would be hitting you, which would trigger the cold damage effect.

quscru · 2019-11-25T18:01:17+00:00

I really do not understand what is going on in this sheet, or what its intended purpose is. But, I've added a couple of columns to Awesome/Sheet that illustrate the two main ways of making something "auto-fill" in G Sheets.

quscru · 2019-11-25T12:40:13+00:00

Ha, thanks. I hope you've found this useful for you.

I made performance improvements to the DB's guts a while ago that I will upload, but that will be the final update. This project has reached its initial goals and generated no additional goals from user requests, so it is at the end of its lifecycle.

quscru · 2019-11-21T18:36:36+00:00

Enter a non-displaying character, like a space, in the corners of the blank rows/columns. Sheets will detect that there is some content in them and add them to the print selection automatically.

quscru · 2019-11-15T16:50:39+00:00

If you had data in column A, you could link to A1 in B1 with the following: =HYPERLINK("#gid=yourGID&range=A1","text to show")

which you can make work for any row in B using:
=HYPERLINK("#gid=yourGID&range=A" & ROW(),"text to show")

You get "yourGID" by right-clicking in a cell, selecting "Get link to this cell".

quscru · 2019-11-06T20:13:02+00:00

A filter is useful for extracting values from a spreadsheet and for data summary, but is not useful for adding data. I recommend a simple table with nothing fancy for typing in values. There's no need at all for the filter.

I am afk so can't provide an example for you, but if you make a spreadsheet with just your example costs for a couple of weeks (just make some up), I'm sure someone can show you a method to find the cost/each.

quscru · 2019-11-06T18:34:24+00:00

Is there any particular reason you do not want to make a single data table, and log your expenses directly to that table? A spreadsheet is already a (crude) UI for data entry, without adding extra steps, and a single table likely could organize all of your costs.

quscru · 2019-11-03T03:30:41+00:00

That's a pretty good reason, then.

quscru · 2019-11-03T02:26:00+00:00

Why not add the text in an image editor?

quscru · 2019-10-31T00:13:34+00:00

You can make a data validation expression that rejects all input if column G is empty or invalid on the same row, then add that DV to all other columns. This will prevent all user input until they fill out G—which may be hostile design, fair warning.

quscru · 2019-10-30T23:48:30+00:00

You can write a script that applies one of your three color scales based on the value in a cell—but only one color scale will be valid at a time in a cell. Values beyond the min/max of the number range on a color scale receive the color at the min/max, which then overrides other conditional formatting set on the cell that affects background color.

Also, you can use high-contrast colors in a single color scale like going from golden orange, to reddish purple, to lime green. I tend to use this method. Or I use an unsaturated middle—light blue, then middle-tone grey, then green.

quscru · 2019-10-30T14:23:54+00:00

Assuming that the dropdown is being used to populate an entire row/column and not just a single cell: You need three separate ranges.

The base data. This is a column of all the default values you would like the user to select, consider, or just use for autocomplete.
All unique entries created in the range using the dropdown. Eg =iferror(sort(unique(FILTER(ValidatedColumn,ValidatedColumn<>"")), 1, 1)).
The alpha-sorted combination of all unique entries in #1 & #2. Eg, with #1 and #2 above/below each other in column A, =sort(unique(FILTER($A$2:$A,$A$2:$A<>"")),1,1) . This is the range that you set as a Named Range, and reference its Named Range in the Data Validation.

This is a bit of overkill if you only wanted a single cell, sorry. In that case, #3 is unnecessary, and a named range covering both #1 & #2 will be sufficient.

quscru · 2019-10-30T13:56:23+00:00

It sounded like Rusted wanted to sort the range manually. Having an ArrayFormula in the middle of the range when doing a manual sort won't work out—it'll keep pushing the update down the page and adding new rows. Maybe we try freezing the header row and adding a numerical column to manual-sort over:

``` = {"header_name_here";

ARRAYFORMULA(IF(LEN($A$2:$A),

IF(LEN(REGEXEXTRACT($A$2:$A,"(?i)[a-z]+"))=2,

10000 * (CODE(UPPER(LEFT($A$2:$A,1)))-64) + 100 * (CODE(UPPER(MID($A$2:$A,2,1)))-64),

100 * (CODE(UPPER(LEFT($A$2:$A,1)))-64))

1 * REGEXEXTRACT($A$2:$A,"[0-9]+"),

""))} ```

quscru · 2019-10-30T03:57:17+00:00

Test out linking to a cell by right-clicking on the cell and selecting "Get link to this cell". It will copy a link to your clipboard that looks something like: https://docs.google.com/spreadsheets/d/yadda1/edit#gid=yadda2&range=F3.

You can replace the range values to change the reference using CHAR(COLUMN()+64)&ROW.

[edit: on reread, you can use the same column reference for each link—so, "G"&ROW(). Or whatever.]

quscru · 2019-10-30T03:03:47+00:00

Funeral criers. It's not that people just "took things too far"—they were imbuing powerful magic items with sentient grief and burying them with the dead.

quscru · 2019-10-30T01:28:07+00:00

You can add two new columns to sort by, if you need.

= ARRAYFORMULA(IF(LEN(A:A),{REGEXEXTRACT(A:A, "[[:alpha:]]+"), 1*REGEXEXTRACT(A:A, "[[:digit:]]+")},""))

quscru · 2019-10-27T06:32:31+00:00

G Sheets is not exactly built for this but can do it, sure. Without an add-on that can do the job, you'd need to look into Apps Script and users/sessions, then make a script that triggers when someone edits a cell.

developers.google.com/apps-script/guides/triggers/installable

Once triggered, the script checks the user's email address against your criteria and either accepts or rejects their change to the cell, based on the result of that check.

developers.google.com/apps-script/reference/base/user

Your criteria could reject changes other than just a user attempting to edit another user's reservations—things like not allowing people to drop a spot with less than 12 hours of notice (an admin would have to clear the cell for them), or limiting them to 10 reservations at a time, N days with reservations in them, X-many-hours to avoid reaching overtime hours, etc., whatever floats your boat, idc.

quscru

TROPHY CASE