Tag: Sheets

How to make Google sheets refresh date filtered pivot tables when a day passes?

Google Sheets has a nice advantage over Excel – their pivot table automatically refresh, while Excel’s pivot tables rely on clicking a manual UI refresh button (or – if the right pivot setting is changed from its default state – by reopening the relevant file).

But Google Sheets’ pivot tables do not get refreshed in certain cases.

If you turn this data into a pivot table:

Name Date Value
Foo =RANDBETWEEN(today()-1, today()) 1
Bar =RANDBETWEEN(today()-1, today()) 2

And add a date filter of today:

Pivot table filtering today

Let’s say luck has it that on the original run you get:
| Name | Date | Value |
| ——– | ————– | – |
| Foo | =today() | 1
| Bar | =today()-1 | 2

Then until the end of days you’ll see Foo in your pivot table, unless you change something else of course.

That’s because Google Sheets’ pivot tables don’t recognize the change of days (when the clock spins and yesterday becomes today, in this case).

Is there a way around it?

google sheets query – Combine two separate, working queries

Trying to combine two separate, working queries, so both can be used on a single sheet.

The idea is a User selects Data for a Resource (sheet TAB), and then either: a Region (Column D) or a County (Column H). Both Region and County are Drop Down lists.

These are the Queries that work separately:

=QUERY(indirect(B2&”!A1:H20″), “SELECT * WHERE 1=1 ” &IF(A2=”All Counties”, “”, ” AND (D) = ‘”&A2&”‘ OR (D) = ‘All Counties’ “),3)

=QUERY(indirect(B2&”!A1:H20″), “SELECT * WHERE 1=1 ” &IF(C2=”All Regions”, “”, ” AND (H) = ‘”&C2&”‘ OR (H) = ‘All Regions’ “),3)

Working Queries are on the first 2 TABs of this sheet

I have tried to join them:
={QUERY(indirect(B2&”!A1:H20″), “SELECT * WHERE 1=1 ” &IF(A2=”All Counties”, “”, ” AND (D) = ‘”&A2&”‘ OR (D) = ‘All Counties’ “),3); QUERY(indirect(B2&”!A1:H20″), “SELECT * WHERE 1=1 ” &IF(C2=”All Regions”, “”, ” AND (H) = ‘”&C2&”‘ OR (H) = ‘All Regions’ “),3)}

Also tried a few things like this with various syntax changes:
=QUERY(indirect(B2&”!A1:H20″), “SELECT * WHERE 1=1 ” &IF(A2=”All Counties”, “”, ” AND (D) = ‘”&A2&”‘ AND (C2)=”-” OR (D) = ‘All Counties’ ” AND (C2)=”-” OR C2=”All Regions”, “”, ” AND (H) = ‘”&C2&”‘ AND (A2)=”-” OR (H) = ‘All Regions’ AND (A2)=”-” “),3)

Also tried similar to above, but used ISBLANK(C2) type syntax.

Thank you for taking the time to try and help!

google sheets – Get historical stock price in another currency

I took a stab at it here:

enter image description here

Column I has a formula like:

=QUERY({$F$2:$G$1000},"SELECT Col1 WHERE Col1 <= date'"& text(C3,"yyyy-mm-dd") &"' ORDER BY Col1 DESC LIMIT 1", -1)

This means that it pulls in the closest currency price date that’s less than or equal to the stock price date.

Column J has a formula like:

=D3/VLOOKUP(I3, $F$2:$G$1000, 2, FALSE)

This means we’re dividing the stock price by the currency conversion price on that nearest date. Hope this helps!

google sheets – How to solve situation where I need timestamp script to work on cell auto entry, not manual

can someone help me with this problem.
I have timestamp script running in google sheet and it work fine when I manually change value in cell. But I can’t find any solution how to have timestamp working when entry in trigger cell is some auto data from formula or such.
Or maybe if someone have another solution.
What I’m doing now is, from sheet1 with Query transfer data to sheet2 when some change is made.
When new row is created in sheet2 I want to have date of entry for every row that is created in sheet2, and date need to be fix all the time.

Formatting Cells in Google Sheets

I am trying to format cells in a google sheet based on columns before it in the row. For example, I have check boxes in B1 and C1, if both of them are unchecked (false) I would like D1 to turn red; but only if 2 in a row are unchecked. So if B1 & C1 are unchecked, then D1 is red; but if B1 is checked and C1 is unchecked, I want D1 to still be white. Is there a formula for this?

google sheets – Is there a way to select a range for a switch statement?

I have a table where one column has numbers from 1 to 25 and another has values corresponding to those numbers.

What I am trying to do is make it so when I change the number in one cell another cell changes to the corresponding number based on the table.

Do I have to do this manually or is there a way to set a range for the cases and values?

google sheets – How to stop IFS function always giving the same result for x even if greater than x or less than x

The order of your IFS needs to be changed.

How the IFS function work:

Evaluates multiple conditions and returns a value that corresponds to the first true condition.

In your formula, since your second condition is C9>=5,"15%" everything greater than 5 will give a 15% result. After that, the formula stops checking the following conditions.

Try the following: 

=IFS( C9>=40, "40%",C9>=15, "25%",C9>=5,"15%",C9<5, "0%")

worksheet function – How to get the max number in a column up to a cell google sheets and excel

This code would be in B8: =if(C8=””,””,1+max(C5:C7))
Then in B9 i would have this: =if(C9=””,””,1+max(C5:C8))
and Eventually ending with: =if(C8=””,””,1+max(C5:C365))
I copied the code to each cell but it auto changes the C5 to correspond with the new cell
I started manually changing each cell so the MAX Function will range from C5 and up but by the time i got to row 40 i found myself on this page. all cells need the max function to start at C5 and then stop at the cell to the right and above.
Is there a better way than copying the code cell by cell and changing the C# and changing it to a C5?