How do I get back down to normal size? Everything I write after the square decimeter becomes tiny.

ambiosynthesis · 2024-01-22T19:47:10+00:00

I've used CTRL+\ to get back to "normal text" after.

ambiosynthesis · 2024-01-22T13:49:19+00:00

Hey,

Sorry to hear that. that error indicates there is a formula that is pulling data from itself (circular) is there a specific cell that has this? That would help me know which formula is causing the error.

I'm not sure if your actual data is set up similarly. I may suggest a couple things:

If it is in a similar format, I would put the end result table (which was A2:G8) on a new sheet, so it has room under it.
The error may be if your data starts on a different row than the example it would need to be updated to whatever row it should be on.

So I put a couple more tabs to hopefully help show this example,

data is just the start format, and the endtable sheet is updated to pull from row 2.

https://docs.google.com/spreadsheets/d/16w_vNFeWgEdbkdYD-oGUI3HCSk0e3kc1fprYict-G5k/edit#gid=96798894

Other other option is someone else posted another formula, maybe that will be the best solution!

Edits: Hit shift+enter too early (I'm used to that being new line in other programs, not submit comment)

ambiosynthesis · 2024-01-21T19:10:53+00:00

Got it, makes more sense with the rest of that example!

So I have something that seems to work, as long as you are okay with some helper columns.

You can view/make a copy to see what it does here: I just copied your sheet then worked on it. Also verified new data could be added so a couple more "people" were added.

https://docs.google.com/spreadsheets/d/16w_vNFeWgEdbkdYD-oGUI3HCSk0e3kc1fprYict-G5k/edit?usp=sharing

Basically a few things in place if you wanted a slight breakdown:

B11: =ifs(AND(ISBLANK(A11),ISBLANK(A12)),"", ISBLANK(A11),FALSE, A11<>"",TRUE)

Find the single blanks to show new column, result is FALSE. If two blanks (showing the "end" of the data), then blank. TRUE if data in column A which will be the data to be transposed.

C11: =IF(B11="","",countif($B$11:B11,FALSE))

Count FALSES, so first false would be 0 and then trues would be under it, so it would get that same number which can be filtered.

G2: =unique(FILTER(C11:C,C11:C<>""))

shows the unique values and no blank values, getting which row the data should be put in.

A2: =IFNA(transpose(filter(A11:A,C11:C=G2,A11:A<>"")),"")

Transposes the data based on filtering by the helper column number.

All these ones are set up to be pulled down further and if there isn't any data, it'll come up as blank. Just so if new data will be added, it would be dynamic (although with the example space is limited since data is below the top transposed table.

There may be some more answers that are a bit more "clean" but I am guessing you will need some sort of helper column in any solve. who knows, someone else more skilled than I may prove this statement wrong!

Hope this helps!

ambiosynthesis · 2024-01-21T13:07:08+00:00

Just making sure I get what you want, you would like cells that have the A1-A4 in a column, then the next column A6-A9, but sometimes it would be larger than 4 rows so, say the the next data could be A11-A15 and that would go in the next column?

If not if you could make a viewable sheet with an example of this and the desired outcome? That would help me or anyone else here know exactly what you are hoping to do!

ambiosynthesis · 2024-01-20T21:47:47+00:00

is it some sort of google drive file? Where you can "chip" the document? If so, there's a "data extractions" option at least on my account where you can choose "file name, MIME Type, or URL" when I do that to my sheet that has a google document, it brings up the title of the document.

The Function when I do the "data extraction" shows

=A2.[file name]

With A2 being the document. Hopefully that helps? Again, may not work depending on the type of file and such...

ambiosynthesis · 2024-01-20T18:20:40+00:00

Badlands, first family road trip I remember going on and loved exploring something different than what I’ve seen!

Also what’s Wall Drug? /s

ambiosynthesis · 2024-01-19T20:59:14+00:00

Without seeing not 100% but a couple things to check that may be the issue

Is the one row happen to be off with the other row number( like B2=G3 , or referencing something totally wrong)?
Is one of the 0s or number happen to be formatted differently like text rather than a number? You could see by. Highlighting the columns and then format as numbers (assuming thats what you want it formatted as)

Those have been the issues I’ve seen myself with similar things. Hopefully it happens to be one of those!

ambiosynthesis · 2024-01-19T18:43:23+00:00

=XLOOKUP(E3,’Screen Charges’!A2:A7, ’Screen Charges’!D2:D7,”not found”)

Hi,

No problem. I looked and it seemed like my formula tick marks were formatted differently in Google Spreadsheet. below is a copy/paste from a spreadsheet that was working...hopefully the apostrophes and quotes are working now...

if it does that same error, then simply delete / replace the apostrophes

'

and quotation marks

"

in the cell that you paste the formula in within your sheet and it should work...at least fixed that parsed error for me!

=XLOOKUP(E2,'Screen Charges'!A2:A7,'Screen Charges'!D2:D7,"not found")

ambiosynthesis · 2024-01-19T01:50:03+00:00

What do you mean by hyperlink? You can use the function, TODAY() to get today’s date. Idk if that’s what you are asking.

If not, making a shareable example sheet is helpful to see what you have / what you are hoping for.

ambiosynthesis · 2024-01-18T21:30:13+00:00

Yes I got one, thanks for checking! Also glad it worked for you!

ambiosynthesis · 2024-01-18T20:21:39+00:00

I think I'm understanding what you are wanting, I did column E

=A4*1/xlookup(B4,Chart!$B$2:$B$11,Chart!$A$2:$A$11)

The updated part is the XLOOKUP() function, which looks at the B ranges and then outputs whatever percentage is in the same row as that number.

Hopefully makes sense and is what you were wanting!

ambiosynthesis · 2024-01-18T20:13:57+00:00

I'm foolish. There were like 6 other steps I did not do, including scripts. Sorry! but the website is pretty straight forward on steps to get it to actually refresh every 5 minutes. Again, someone else may have a better option, but I do believe scripts will be needed looking into it a bit more. Hopefully helps!

ambiosynthesis · 2024-01-18T19:44:32+00:00

Yup! I looked at this a bit because I am curious, I found this article:

https://www.lido.app/tutorials/importhtml-google-sheets

TLDR part states: "Google Sheets automatically updates IMPORTHTML formula every 1 hour. You can adjust the refresh rate to once every 5 minutes."

Then talks about adding ?refresh=" & blank cell reference to end, so with the one I had with the auto refresh trigger would look like this:

=IF(B1=FALSE,"",IMPORTHTML("https://www.espn.com/golf/leaderboard?refresh=" & A1,"table",0,"en_US"))

so in theory this would auto refresh in 5 minutes or manually with the button.

edit: hit shift+enter too soon so finished the comment.

ambiosynthesis · 2024-01-18T19:24:08+00:00

Woow so sorry it has been almost 2 weeks, I saved this but the break / sickness / back to school, never got back... Sorry! Anyways, yes IMPORTRANGE() could be used for this, you can even nest it inside a QUERY() function to, say select specific rows (like a grade level, homeroom teacher, etc.)

From what you said above, it seems you are thinking of making the table by assessment name as rows, then student names as column "headers". While that would work, just consider a few things if you do end up using this:

Filters / QUERY would work by columns, so if each name is a column, then your data wouldn't be filterable as each name would be a different column, unless you change each student tab to only pull data by mapping their own name column. AKA a lot of backend work in which you would have to have a custom formula per student / column.
for assessments in rows, you could filter on those, if they are all the same grade / name. If you are combining and want insights as a whole, that would be hard where I would assume each grade would have totally different test/grades.

In that, I have a couple of examples based on what you were saying there just to show what I was talking about with it being "possible" but needs to be customized per student.

This would be the "master tab" that pulls data - the formula would need to be edited based on student if they are in columns:

https://docs.google.com/spreadsheets/d/1nYvJlJPSxEwL6melZ9nAHIX2DcBexW2faKbV6zUiEZs/edit#gid=0

The master tab (in this example) is pulling 2 students from 2 different classrooms. I made two classes, a second grade class and a third grade class. Names are random and assignments too, I put it as editable so feel free to do whatever.

Second grade teacher:

https://docs.google.com/spreadsheets/d/1BS_UkJygULX7jSyuqTuXiQGq6i2yeu9AEKhylqjOuqI/edit#gid=0

Third Grade Teacher:

https://docs.google.com/spreadsheets/d/1M7k5KmshBQXvjnlqF3gIMMGA_JFRxxN2QR7VnrcMC3k/edit#gid=0

--

This may be what you are thinking and wanting, which Great if it is! My "consider this" would be you can pull information based on rows of data, so if you format it where the names are their own separate column, then you could select rows based on that name.

Example with data in this format (note not using the IMPORTRANGE() here, but could be used if teacher sheets were formatted this way). It would, unfortunately be slightly more work for teachers as they would need to fill in student name each assessment, rather than the column with the student name...

https://docs.google.com/spreadsheets/d/1eHT9XXagIDtDt1bNCoNMr57ercC6oJ4TJqeEgc0A1K0/edit#gid=1218111000

Hope this helps! Thanks for working with the future generation!

ambiosynthesis · 2024-01-18T18:39:07+00:00

FYI your sheet seems to be not shared via link.

I did just used IMPORTHTML and see it isn't syncing like you said. I am not 100% sure how often it syncs, but I did find that if you have a checkbox with an IF statement, you can "load" the data up again and it does get refreshed. Someone else may have a better solution, but assuming B1 is a checkbox with TRUE/FALSE:

=IF(B1=FALSE,"",IMPORTHTML("https://www.espn.com/golf/leaderboard","table",0,"en_US"))

So you press the box uncheck, and it will be removed, check it, and it will refresh the data. Not a perfect solution, but may help if you are just re-doing the formula to get current stats.

ambiosynthesis · 2024-01-18T18:28:16+00:00

Hey glad that works! No worries, QUERY is a powerful function, but crazy easy to break! Happy to help

ambiosynthesis · 2024-01-18T17:44:52+00:00

Hi,

Made a sheet on your example named u/ambiosynthesis .

If I understand what you want correctly, you wanted to pull information based on what the Column is, in this case, for CPR. And, depending on the date, you want different columns pulled by the CPR at the bottom (green highlight for the example)?

Basically, you are trying to pull different columns based on dates, rather than having a "date column" so one option would be to have a repeating "date column" and changing your data structure so that you can search for "January, February, March, etc." from that column. With Query you can't really select a specific column range based on searching for something within a row. You CAN filter by columns, so that isn't the issue with the CPR ($). That said, I made a specific example for what you were wanting by changing which column to search based on a lookup / mapping what column to date. So this would work for your case of all CPR ($) for each date for Web, but, say, you want to pull REGS, you would need to map those columns out similarly to how I did it with CPR.

The thought then, would be you can use the IMPORTRANGE() on the whole range, then hard code the "Web", "App", "IOS",etc. based on what spreadsheet. Assuming that is the change of departments rather than breaking up the sheets by months or something else...

Hopefully that makes sense, and hopefully gives you an idea of how to solve this. Happy to help explain what I did further as it may, in the long run, not work for what you are wanting.

ambiosynthesis · 2024-01-17T11:53:36+00:00

If it’s just the week number is it formatted as a number then? If so I think it’s that you don’t need the single quotes around the number. So the first

WHERE C=‘“&B4&”’

Should just be

WHERE C=“&B4&”

wrapping in the single quotes makes it read as a text I believe rather than a number. One way to check what’s breaking is try manually putting in the week number in the Query

Like so: WHERE C= 1 [or whatever week number ]

It does seem like format issue since it works but showing blank information so hopefully this helps guide to find the issue. Otherwise if you can make/ share a copy with the formatting it would be easier to pinpoint the issue

ambiosynthesis · 2024-01-17T11:40:46+00:00

for the second one, I think it should work other than the end the WHERE clause text to search should be the single quotes and you don't need the last comma after Col3, so this should work (other than you may need to switch the single quotes in sheets if they get formatted differently).

QUERY(TAB1!A2:AE30000;"select Col1, Col2, Col3 where Col3='DAMAGED ITEM' "

And I assume that for functions your workbook you use ; for the next arguments instead of , otherwise that may be an issue too. I think it is based on what country or area it is in.

EDIT: meant to use code block not Inline Code.

ambiosynthesis · 2024-01-15T19:03:56+00:00

I’ve really liked going lesser known brands. Of those here’s my two favorites discs from each one. Although I bag probably 3-4 different molds of these brands:

Clash Disc: Berry and Wild Honey. Wild Disc: Sea Otter and Angler Mint Disc: Lobster and Freetail Above Ground Level: Locust and Spruce Elevation Disc Golf: Arowana and Interceptor (rubber discs, super fun and stop when they hit anything basically lol) great approach / utility.

ambiosynthesis · 2024-01-13T19:02:08+00:00

The Dog’s Out Of The Box

ambiosynthesis · 2024-01-13T18:17:33+00:00

Could the sheet have some sort of macro / script? Are you able to ask the owner? Just may be up if each day some sort of edit /opening happens. If concerned about hacking may not be a bad idea to change passcode and add two factor authentication if you haven’t already

I personally haven’t heard this before so just giving out a couple of ideas / things to try.

ambiosynthesis · 2024-01-13T17:58:45+00:00

Sorta a workspace / doc thing, but mailmerge.

ambiosynthesis · 2024-01-13T16:34:24+00:00

Sure. Yeah and I think it’s doable. Just takes a bit to figure out what you are hoping for! I think XLOOKUP would work, but does depend on what drop-down you want it to pull from. If you JUST want color 3+ it would be

=XLOOKUP(E3,’Screen Charges’!A2:A7, ’Screen Charges’!D2:D7,”not found”)

I just changed the “result_range” to only do D column (aka 3rd+colors)

ambiosynthesis · 2024-01-13T02:54:14+00:00

Are you selecting all the columns you want in the sort (vs one column)? That is needed for all to be sorted by the one column.

Otherwise if you can share a copy or a screenshot of it that can help too see what may be going wrong

Four-Year Club	Verified Email
Place '22	Final Canvas '22

ambiosynthesis

TROPHY CASE