Google Sheets help

[talmid chuchem]

As the world is progressing more & more towards cloud based software, I think that it's time to have a Google Sheets help thread, like there is a Microsoft Excel help  thread.

[talmid chuchem]

I'll start: anyone with google apps script for converting Hebrew dates into Gregorian dates?

To Explain why i would need this: I would like to have a sheet with things that recur yearly on the jewish calender, & get a reinder a few days before. For example i would like to put into the spreadsheet chanukah start date, I would put in כה כסלו in the date column,  in description column will be Chanukah starts, & in reminder column will be how many days before i would like to get reminded.

Thank I'll have the script checking for כה כסלו & the year will be added in the script (as the year changes & i want to get reminded every year), & the script will calculate the Gregorian date based on the hebrew date that the script will feed it (date column & year), & then the script will check the criteria set in the third column if it should send a reminder email.

[lubaby]

Why not just download something like this to your google calendar and use the calendars reminder feature for notifications?

https://www.hebcal.com/ical/

You can customize the calendar before downloading to have all your pertinent info.

[grodnoking]

I'll start: anyone with google apps script for converting Hebrew dates into Gregorian dates?

To Explain why i would need this: I would like to have a sheet with things that recur yearly on the jewish calender, & get a reinder a few days before. For example i would like to put into the spreadsheet chanukah start date, I would put in כה כסלו in the date column,  in description column will be Chanukah starts, & in reminder column will be how many days before i would like to get reminded.

Thank I'll have the script checking for כה כסלו & the year will be added in the script (as the year changes & i want to get reminded every year), & the script will calculate the Gregorian date based on the hebrew date that the script will feed it (date column & year), & then the script will check the criteria set in the third column if it should send a reminder email.

I downloaded a random Hebrew calendar app and it synced all the holidays to my calendar.

[talmid chuchem]

I downloaded a random Hebrew calendar app and it synced all the holidays to my calendar.

I don't need it only for holidays, I need it also for yurtzeitin.

[etech0]

Great article:
How to Import Data from Any Web Page into Google Sheets with ImportXML

[Yammer]

Any way to encrypt a column in Google sheets?

[etech0]

Any way to encrypt a column in Google sheets?

not AFAIK, but I'll be happy if someone can correct me.

[etech0]

Is there a way to protect a sheet from editing, but still allow people to hide/unhide columns?

[lubaby]

Is there a way to protect a sheet from editing, but still allow people to hide/unhide columns?

Hiding columns is considered editing, so I’d venture to say no.

[ponash123]

Anyone know if there's a way to do ctl ditto in Google Sheets?

[etech0]

Anyone know if there's a way to do ctl ditto in Google Sheets?

There's something similar: https://webapps.stackexchange.com/questions/74517/equivalent-of-ctrlapostrophe-in-google-sheets

Basically you need to select the cell with data to copy, and the cell below it where you want to paste, then press control-d

[ponash123]

There's something similar: https://webapps.stackexchange.com/questions/74517/equivalent-of-ctrlapostrophe-in-google-sheets

Basically you need to select the cell with data to copy, and the cell below it where you want to paste, then press control-d

Thanks!

[etech0]

Are there any formula experts here?

I'm trying to append a total row to a QUERY formula with a pivot. It's complicated but the actual formula works B"H, I just need help with the total row. I'm using {} to append the row.

The total row has to do some subtraction, so I'm putting in there SUMIF based on the data that's somewhere else. The SUMIF works great if I manually type in the column letter for each column. Looking for a better way, I thought I could use the COLUMN() function. Problem: the COLUMN() function seems to refer to the column in which the function is placed, not the column where the result is displayed.

For testing purposes I put this in column I, and got this result:

Code: [Select]

={column(),column()}9    9

Anyone know of a way around this? I want the second 9 which is displayed in column J to show a 10.

Here's my entire function/formula in case you need it, but the question stands even without it:

Code: [Select]

={QUERY(Subsidies!A:F,"SELECT E, SUM(D) WHERE B <> ''  group by E pivot(F) ORDER BY E DESC");"TOTAL DUE",-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C))}
TIA!!

[lubaby]

Are there any formula experts here?

I'm trying to append a total row to a QUERY formula with a pivot. It's complicated but the actual formula works B"H, I just need help with the total row. I'm using {} to append the row.

The total row has to do some subtraction, so I'm putting in there SUMIF based on the data that's somewhere else. The SUMIF works great if I manually type in the column letter for each column. Looking for a better way, I thought I could use the COLUMN() function. Problem: the COLUMN() function seems to refer to the column in which the function is placed, not the column where the result is displayed.

For testing purposes I put this in column I, and got this result:

Code: [Select]

={column(),column()}9    9

Anyone know of a way around this? I want the second 9 which is displayed in column J to show a 10.

Here's my entire function/formula in case you need it, but the question stands even without it:

Code: [Select]

={QUERY(Subsidies!A:F,"SELECT E, SUM(D) WHERE B <> ''  group by E pivot(F) ORDER BY E DESC");"TOTAL DUE",-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C))}
TIA!!

Looked through this? You're using the default () value instead of putting in the reference you need.

https://support.google.com/docs/answer/3093373?hl=en

[etech0]

Looked through this? You're using the default () value instead of putting in the reference you need.

https://support.google.com/docs/answer/3093373?hl=en

Thanks for answering! I hadn't seen that. The reason I'm not putting in the reference I need is because I want to use the INDIRECT function to refer to a cell a few columns over, and I want each column to have a different result. Do you know a better way to get the value of a cell a certain # of columns over?

[lubaby]

Thanks for answering! I hadn't seen that. The reason I'm not putting in the reference I need is because I want to use the INDIRECT function to refer to a cell a few columns over, and I want each column to have a different result. Do you know a better way to get the value of a cell a certain # of columns over?

Maybe OFFSET.

https://support.google.com/docs/answer/3093379?hl=en

[etech0]

Maybe OFFSET.

https://support.google.com/docs/answer/3093379?hl=en

It looks like OFFSET needs you to provide the address of the current cell (or the cell to refer to) - how do I tell it to use the cell that the result is displayed in, rather than the cell that the formula is in?

[as2]

Looking for some help with a basic formula. I have a spreadsheet that has a name in column A and a balance in column B. My goal is to have column C give me a specific percentage of the cell in column B. Is there a general formula that means "the previous cell in this row" or do I need to manually adjust the formula for each cell in the column?

[davidrotts63]

When you paste it below it should follow the pattern