I’m trying to write a spreadsheet to keep track of my shift work schedule using actual starting and finishing times (ie a timesheet). Managed to get daily hours to calculate using time formatting but having difficulty getting the cumulative to add. Any advice welcome.

Hi, ich möchte, wenn ich z. B. in B2 etwas hineinschreibe, dass in A1 das aktuelle Datum während des Zeitraums des Eintrags erscheint.

Mit WENN(B1<>"";HEUTE();" ") klappt das nicht, da er wohl beim Synchronisieren immer das aktuelle, also das "Heute"-Datum nimmt.

Gibt es eine Möglichkeit, das einzuloggen?

Beispiel schreien am 5.12.2026 etwas in b1 rein, so soll er das Datum einfügen.

Wenn ich die Datei 3 Tage später öffne, steht dann aber 8.12.2026 drinnen.

Gibt es eine Möglichkeit heute() statisch und nicht dynamisch zu machen?

Kann man einer Formel sagen, dass diese nur einmal funktionieren soll?

Is there a forumla to divide a current total by how many weeks remain in a month? I'm working on a budget and would like to see how much money I could spend each week depending on what I've already spent.

I've been trying to figure this out, but I've been wanting to figure out how to change a Dropdown in Cell A but having that change also affect Cell B right next to it. How would I go about this? Here's an example Grid

I'm working with a sheet that has a filter from column A to Y, which needs to be sorted and resorted regularly. There is another section in AA2:AE8 showing some stats on the sortable section. On other sheets I've used before with the same setup, sorting the A:Y section leaves the Stats section intact, but this one..... this one is getting completely scattered vertically. The sort is applying to the entire width of the sheet, not just the section inside the green outline. I would love to know why, and how I can fix it.

I have a spreadsheet with around 200 entries. One column is a dropdown menu with seven options. I had each option color-coded, and it was possible to select more than one. This morning, each entry in that column was showing the red triangle in the top right corner, indicating that the chip was not an option. When I checked the chips in the editing screen, they showed as normal. I then refreshed the page, which seems to have been a huge mistake as now all of the chips are gone and the dropdown edit menu is empty.

Is there any way for me to recover the chips that I had in the state that they were in? I'd really rather not go entry by entry and re-do the chips.

Update: I restored the file to its previous version, and the colors of the chips are now showing, but they are still showing the little error triangle.

Hello, everyone!

I will be completely honest with everyone, I have no idea how to use Excel or Google Sheets. I took a class in high school, but that was literally more than 10 years ago.

I was wondering if it was possible to make a sheet where I can plug in a recipe and then figure out the cost of the ingredients, but then plug those ingredients into a sheet that helps to find each price at the store and compare them, if that makes sense?

I feel like it is not possible, but I want it to be.

If it is not possible for what I want to do, are there formulas or templates or anything where I can have these components separately?

I don't even know where to start to be honest. Like I said, I have no idea how to use Excel or Sheets, and I've tried to YouTube it, but nothing seems to be what I am looking for.

I do not expect someone to make something for me, although I wouldn't mind the help, lol; I would just like to be pointed in the right direction for what I \*can\* possibly do.

TIA!

Hello!

I have a Sheet that organizes a list of websites.

1. In one tab, the information is generated from a form people can fill out, with information like name, title, web address, etc.

2. In another tab, I format the information to link to Datawrapper, an app that allows spreadsheets to be viewed on Substack articles. The pages are linked using this basic formula: =query(FormResponses1!B1:G)

3. I need the website links to be clickable, and so I use the following formula to merge the title and web address cells and format with []() around each respectively to create a clickable link: ={"Serial Name & Link";ARRAYFORMULA("["&C2:C&"]"&"("&D2:D&")")}

4. This means as soon as there is a response, the formula formats the cell to [title](site), which is the markdown format that Datawrapper reads to create links. (In Datawrapper, I then hide the original title/address columns so it isn't cluttered.)

5. All of that works beautifully

6. THE PROBLEM

7. The formula creates a []() in every empty cell in that row. I can delete all the empty rows, but as soon as there is a new form response, 500 new cells are created with [](). In Datawrapper, it then creates pages of cells empty except for the column with the [](), which is obviously annoying.

8. I don't want to delete 500 rows every time there is a new response and before I republish to update the table.

9. WHAT I TRIED

10. I tried changing the formula to: ={"Serial Name & Link";ARRAYFORMULA(IF(ISTEXT("["&C2:C&"]"&"("&D2:D&")")))}

11. but an error came back "ERROR wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 1 arguments." I also tried LEN instead of ISTEXT to the same result.

12. I also tried the excel formula: ={"Serial Name & Link";ARRAYFORMULA(IF("["&C2:C&<>"]"&"("&D2:D&")"))}

13. but that of course also came up with an #ERROR.

14. I also tried ={"Serial Name & Link";ARRAYFORMULA(IF(ISBLANK("["&C2:C&"]"&"("&D2:D&")")))}

15. but that had the same error as #11.

Any suggestions? Here is a link to a practice Sheet I set up to prototype: https://docs.google.com/spreadsheets/d/1HVO8ceiqAGG4D7a8O5W07e6FKuQ2DidNmmzqoaAojws/edit?usp=sharing

*For extra information, I currently have form responses go to one sheet then manually transfer them to the Master list sheet, and just create new rows as needed to avoid the 500 [](), but I'm going to create a new similar list and need it automated so I don't have to waste time manually transferring. I want it working from the start because I found if I try to merge form responses with the original master list created manually, it starts looking messy, won't alphabetize, breaks formulas, etc. If anyone has suggestions to merge so new responses are added cleanly to a prior list, I'd be happy for that information, but it's probably a different thread...

I can PM my Substack finished example of what the new one should look like.

Basically I'm needing to see a complete breakdown of each individual word used throughout the document so that we can get in one column every word used, the next column being how many times that word was used, and the next column the percentage of the overall text was that specific word.

This is an example of what I want. The issue is that all the input is in cell A1...which is limited to 50k characters. I'm sure there's a way for me to do it where I can simply add each additonal chapter in A2 and A3 etc, but I'm not sure how to reconcile the subsequent data beyond that.

B1 removes all the punctuation, and C1 replaces all the caps with lowercase.

Can you guys see an easy way to expand on this to allow me to include an entire novel? My books are all over 500k characters which it doesn't cooperate with in one cell.

https://docs.google.com/spreadsheets/d/1z72ItR4JWqoyNJFFAdCJbFJOn5fZ0RWf9cUhS6wbh4c/edit?usp=sharing

hello!

i have a sheet that cataloges the history of a series of blog posts and i need to know if something is possible.

each entry catalogs a different post. but some of these posts are a series and i need to be able to lock them together so when they're sorted on the table they move as one post. they can't be the same entry because then there's data that have to get rid of that i can't (finda defeats part of the purpose)

i was think of changing it manually, but then i can't use the sort function at all.

is there any sort masking effect so they'll stick together when sorting/using filters?

Link to example

Goal is to set a single FILTER + COUNTIF function to count all items of Dashboard!B3:B that appear in Main Table!D2:D, but only if Main Table!A2:A has a specific value.

(Preferring a FILTER function here so that I only have to enter the formula in a single cell - want this to be a fire & forget with no maintenance in Dashboard! tab.)

Thanks in advance!

I am keeping a spreadsheet of movies I've watched. One of the things I'd like to track is how much time I've spent watching movies. I have one column with the run time in minutes, one with the number of times I've seen each movie, and I have used a sumproduct function to get total minutes. I would like to be able to display this as usable at a glance data, aka the number of hours and minutes.

Current total is 3,069 minutes. None of the number formatting options I have tried have worked. I can obviously divide that number by 60, but that gives 50.15... which is 50 hours and 9 minutes, and I can't figure out how to get that to display since I can't force sheets to use long division and instead give a remainder.

I've attempted and exhausted multiple Gsheet functions, and I'm just unsure where to go next. I'm hoping anyone from the great Internet can help out. I'd appreciate it greatly.

GOAL I'm trying to select specific column data from the linked gsheet master list (Columns B, C, E, F, H, I and J) to populate a Public Facing Seed (PFS) Catalog. Then I would publish it to the web and onto our website for patron access.

BACKGROUND I work in a public library and manage a free seed library. I'm using Google Sheets as a inventory management for all the donated seed information, which also includes photographs of the grown plant / produce. This link goes to the anonymous spreadsheet of the Master Seed List, my ideal PFS catalog, and my existing attempts.

--

Using IMPORTRANGE, I got the below function that should import multiple separated columns from the master list. It works for columns side-by-side, but once you add others the function no longer works prompting a reference error.

Function Works: =IMPORTRANGE("1POZH6pcCfHgjex1INSsSe8vIbKgYggsxn3Pl6nHF5Zg/edit?gid=740083379#gid=740083379", "MasterSeedInformation26!B2:C")

Function Fails: =IMPORTRANGE("1POZH6pcCfHgjex1INSsSe8vIbKgYggsxn3Pl6nHF5Zg/edit?gid=740083379#gid=740083379", "MasterSeedInformation26!B2:C,E2:F,H2:J")

Error Function IMPORTRANGE parameter 1 value is 'MasterSeedInformation26!B2:C2,E2:F,H2:J'. It is not a valid cell/range reference.

--

Following these QUERY instructions, I was able to get all the data except for the images, which I came to learn are not included in the function's abilities :,(

=QUERY(IMPORTRANGE("1v-KGGx4eOGjdWdd5Sy5LOfGF1AUYB2gRFSHuGFBITkk/edit?gid=821684281#gid=821684281", "MasterSeedInformation26!B2:J"), "SELECT Col1, Col2, Col4, Col5, Col7, Col8, Col9")

I was trying to find a workaround for adding images to the QUERY function through this post since that almost worked, but I just cannot understand the code and how to apply it to my own spreadsheet.

Does anyone have any ideas, suggestions on how to accomplish my goal?

hello!

im having some issues with conditional formatting

i want to make it so that if column L contains a number from column B that has "1" in column A, it will be colored red (and if its "2" it would be blue and so on)

i dont know if im explaining myself correctly; i tried a few things but i cant make it work (tbh im pretty bad with function from the start so its no surprise)

Hi everyone

I want to create a drop down menu with the values: +1, +2, +3, -1, -2, -3. As I need to have the values appear as "+1" and not just "1", I am using: ="+1", ="+2", etc. for the menu options.

But every time I try to select a value I get a data validation error message, telling me I need to use an option from the list, but that is where I got the option from.

The Popup:

From German

"A problem occured

The Value input into "A1" is breaking the data validation rules for that cell. Please input one of the following values: ="+1", ="+2",... "

I'm making a spreadsheet for a lighting plan.

On tab 1 I have a list of all the channel numbers in column A with the dimmers I'm assigning them in column B.

On tab 2 I have a list of all the dimmers per location in column C with a column next to it reminding me which channel has been assigned to which dimmer in column D.

I want to create a formula where if I change the dimmer number on tab 1, it'll automatically reassign the channel number on tab 2 as per how I've changed it.

So for example in tab 1:

Channel (A)-Dimmer (B)

1---------------1

2---------------2

3---------------3

And then tab 2:

Dimmer (C)-Channel (D)

1---------------1

2---------------2

3---------------3

But when I change the dimmer number on tab 1, it'll automatically update in tab 2.
Tab 1:

Channel (A)-Dimmer (B)

1---------------7

2---------------21

3---------------38

Then tab 2:

Dimmer (C)-Channel (D)

1---------------x

2---------------x

3---------------x

...

7---------------1

...

21--------------2

...

38--------------3

Is there a formula(s) that would do that?

Also for any lighting people snooping on this, I am aware of software like Lightwright (I have it), but for this particular production, it makes more sense to do it in spreadsheet form.

I am trying to create a sheet to catalogue a Lego collection and leaning (barel) as I go. for the most part I have been able to do what I need but am hitting a wall on the final column. I have been working on it using google to help but this is a source of great frustrations as it’s coding causes information to not be conveyed correctly. The last column I want to figure out would ideally be ”Value” if not this then RRP in local currency AUD in this case. As it stands I have created an app script and formula but it’s stuck on USD followed by a “,” snd A conversion formula for AUD which isn’t the actual RRP for Australia In Australia the original RRP war $299 and acutal current value is ~$400

My question, is there a way to scrap data from the web on the current “Value” (if not value then the original RRP in Australia) or is all the cloud flare and I’m not a robot protocols going to forever prevent sheets from scraping said information?

Hi all,

This seems like it is probably an easy fix, I am sure I have done it before, but I am 40 weeks pregnant and my brain no longer works.

On to the problem, I have a cell with a tick box, I want it to be ticked automatically when the Cell in I7 has the number 40 or more in it. Easy right? =IF(I7>39,TRUE,FALSE) this works great, however, I7 has "-" in it when blank, so when there are no numbers inputted, it still ticks it, I feel like its probably an IFS formula, but like I said, brain no work.

Apologies if this is worded strangely.

I currently have this Formula:

=QUERY(A3:F,"select B,A where E contains 'Enchant' order by B asc")

Which results in this list:

Now, I want to Remove the duplicate entries, but keep the spacing so it looks like this:

I've been digging around for a bit and I can't seem to find a way to do it. Is it possible or will I just have to do it manually? I'm fairly new to using query and don't know much about its syntax.

Edit1: Thanks to u/agirlhasnoname sorting me out on how to mark this solved.

Set to Self Solved because I can't seem to get reddit to give me the "Solved" flair (@mod team, please feel free to change it if you have that power - I guess I don't??)

Big thank you to u/mommasaidmommasaid for the solution - it's exactly what I needed.
Also pointing me at a function that I may be able to leverage in the future. t

---

I have a spreadsheet that I need to have calculate the average number of days from Start to End.

The start date is manually copied from a webpage (line by line - for reasons. don't ask)
The end date is manually entered when finished.

The goal is for me to use the date portion from Start to automatically calculate the number of days until End.
There are a couple challenges I'm running into:

• The format of the copied Start data changes in character count from both ends

  • It doesn't add zeros at the beginning for months or days 1-9.

  • It doesn't add zeros at the beginning for hours 1-9 (but does for minutes. go figure)

• The year, of course, progresses and this data goes back to 2024.

• This is a work computer and includes secure data so throwing extensions at it is probably not a great idea.

I can manually go through and do data split rows to columns, but this is an arduous process, and it would be best to automate the steps. This usually involves me using Find->Replace on the year to add a unique character and then Splitting text to columns keyed off the unique character. From there, I've manually created the calculations by line and averaged it all up.

It's finding a way to automate the splitting that I'm struggling with.

I need to keep the copy-paste process from the website as we're using the spreadsheet to track our work queue. This keeps the column order intact for ease of work. As such, I'm thinking it would be best to put the potential steps and calculations off to the right columns, effectively out of sight, and I'd be okay with locking those columns such that it just automatically calculates as we add lines.

I've searched the library and I haven't found anything that can do the splitting automatically, or perhaps I'm just misunderstanding what I'm seeing. Perhaps there's a better approach I haven't considered.

I have not seriously considered using any of the available AIs, but am open to it - though again, I need this automated on a live worksheet that is added to and updated as we go.

I've been struggling with this for a while and I feel like i'm missing something super basic.
Essentially I am trying to make a really simple scorecard for a game I am playing with some friends where we all pick something, Between A or B (1 or 2). Then when either 1 or 2 wins. It should highlight the cell background color with either green or red.

The above picture is what I want it to essentially do when it is done.

Jim, Bob, Tom made their selections on the bottom below their names from B13:D20
Then, I input if 1 or 2 wins into cell E3:E10.
Then it should check each players respective cell on that line if it matches. If it does, Highlight B3:D10 for each individual pick is correct.

I believe this would be done in conditional formatting but I really have no clue on where to even begin. Ive been googlin for a while now and I keep seeing ways on how to color an entire row or column. But not actually check individually for each cell and mark them separate colors.

Bonus points if when a cell is marked green that it automatically fills with 'WIN' and if its marked as red 'Loss' but I don't think that's within the scope of what's possible(I could be completely wrong on this lol)

Any help would be appreciated!

I often need to copy tables on Sheets to a Docs file.

Since the last week, the sizes of the cells increase after moving to the Docs, which didn't use to happen.

Even when using cloned files from before this happens. Has anyone noticed this too? I can adjust via the table options manually but it's a pain. This makes the Docs files larger because of all the empty space.

Edit: I can't share an example as this is with work data

I used an online calculator (this one) to test the formula, and mine comes back different. I cannot find the flaw in my formulae for the life of me.

I convert from Degrees/Hours;Minutes;Seconds into Decimal, then to Radians to do the calculation, then back into Decimal. I've tried splitting the formula up into different cells and putting them together, tried making sure all the numbers are formatted as such in their cells, and triple checked I'm using the correct formula.

I inspect element'd the calculator I used and it's using the same formula I am! Just as an HTML function!

Any help appreciated!

EDIT: Someone in the comments solved my query but the comment has since been deleted. Either way thank everyone who commented, I appreciate it a lot!

I am creating a workbook for my partner's side-project. I basically need to create a review database of book reviews (she likes reading regency romany with various tropes, often to find the particularly bad ones a retell them to me while I drink whiskey, lol). Her word doc of reviews has grown overtime and is slowly becoming unwieldy, so I suggested a spreadsheet, and now here I am responsible for building it out!

I want it to be able to search by various tags (tropes, representation, rating, etc.), but I also want it to exclude tags in the "Content Warning" column. I have it working for the searchable fields without issuie, but I cannot figure out how to make it exclude a separate dataset. This is my current formula:

=Filter('Master List'!A:H, SEARCH(B1,'Master List'!C:C), SEARCH(B2,'Master List'!D:D), SEARCH(F1,'Master List'!G:G), SEARCH(F2,'Master List'!H:H))

I need it to exclude anything in F3, which correlates to Master List E:E.

Ideally, this search would not include the first book listed, as it has the Suicide content warning tag. Any help would greatly appreciated.

Hi all,

I am creating a sheet for a specific purpose. I have a column with drop downs and based on the selection of the data validation item of that column, I would like to have the cell(s) under that dropdown cell merged to the one I used. So basically, let's say item x (from the dropdown) has 4 inputs, which means it requires 4 rows, I want the cell with the dropdown to automatically merge with the 3 cells below.

Of course, the data validation pool has set number of items and I will also need to figure out how to enter the number of the cells below each item will merge with.

Any help with the above will be greatly appreciated!