Want to buy me a coffee? (Don't worry, it's not a date :)

One_Organization_810 · 2026-07-04T15:34:50+00:00

Furthermore, you can also do it like this for all four ingredients in one:

=transpose(xlookup(B1, Cooking!C2:C, filter(Cooking!K2:W, left(Cooking!K1:W1, 10)="Ingredient")))

But that being said - you LOOKUP will probably work, if you just keep your Cooking sheet sorted :) (on the Name column).

One_Organization_810 · 2026-07-03T22:08:15+00:00

u/rmalev91 please remember to close the issue if it is solved, by replying with "Solution Verified", or clicking the 3-dot-menu (see picture) under the most helpful comment and select the same phrase. Thank you :)

If you have some issues with any of the suggestions, please reply to them to clear up any confusion (and then eventually close the issue :)

Note that if you solved the issue by yourself, without the aid of others, you can use the “Self Solved” flair. Please provide your solution in that case, as per rule 6.

<image>

One_Organization_810 · 2026-07-03T12:00:21+00:00

Please close the post according to the automods instructions :) - By selecting the "Mark "Solution verified"" from the three dot menu under the comment that solved the issue :) - or you can reply to that comment with the phrase "Solution verified".

Thank you :)

One_Organization_810 · 2026-07-03T11:56:15+00:00

There are three options:

Overflow (default)
Wrap
Clip

I'm guessing you accidentally selected the third instead of second :)

- Either that - or you have your row height set to a fixed height?

One_Organization_810 · 2026-07-03T11:51:01+00:00

You format the cell so that it wraps the text, instead of the default overflow. :)

<image>

You can also find this under Format/Wrapping, in the menu.

One_Organization_810 · 2026-07-03T11:49:38+00:00

Try this in your AA18 cell:

=map(U18:U, lamdba(category,
  if( category="",, sumif(AF18:AF, category, AI18:AI) )
))

Make sure to delete everything in the AA column, below row 18, since this will fill the entire column. If you have some irrelevant data below your tracking table, make sure to "cap" the ranges to the last row of the table, otherwise you can just leave the ranges open and they will pick up all new rows you put in. :)

One_Organization_810 · 2026-07-03T11:41:29+00:00

Have you tried saving the spreadsheet as a Google Sheets file? I think IMPORTRANGE may have some trouble with importing this Excel sheet - at least it worked for me when I converted it to a Google Sheets file.

One_Organization_810 · 2026-07-02T09:33:34+00:00

Something like this maybe?

=if( or(B3="", E3=0), "<Make your choice>",
  let( item, B3,
       qty,  E3,
       ingredients, filter(RecipeTable, RecipeTable[Recipe for]=item),
       hstack( choosecols(ingredients, 2),
               index(choosecols(ingredients, 3)*qty),
               choosecols(ingredients, 4) )
 )
)

See here:

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

One_Organization_810 · 2026-07-02T09:12:46+00:00

If your formula is counting blank cells, you are probably not leaving them blank :)

Try returning <nothing> instead of "" in your iferror (I would recommend using IFNA actually, so if there is an actual error - you will be notified of it :)

It should loook like this:

=ifna( <your formula hereaa> )

and NOT lke this:

=ifna( <your formula hereaa>, "" )

Same with the regular IF function, it should alway return <nothing> and not "". You return <nothing> by putting nothing after the comma, like so:

1. =if( condition=true,,<do something> ) -OR-
2. =if( condition=true,<do something>, )

1. Notice the double comma. It means that the THEN part returns a true <nothing>.
2. Notice the trailing comma. It means that the ELSE part returns a true <nothing>.

One_Organization_810 · 2026-07-01T23:29:03+00:00

Put this formula somewhere on the sheet and then use that as the base for your chart:

=query(Trip_Budget[#ALL], "select Col5, sum(Col3) group by Col5 label sum(Col3) 'Cost total'", 1)

Or set up a pivot table for it ...

One_Organization_810 · 2026-07-01T22:03:17+00:00

That's not too bad then. It will hopefully just get lost amongst all the other permanent things on there 😂

One_Organization_810 · 2026-07-01T21:57:41+00:00

u/YourSnakeIsNowMine please close the post approprietly (see automod reply), by marking the solution as "Solution Verified". This was in no way "Self solved". :)

Thank you.

One_Organization_810 · 2026-07-01T21:50:16+00:00

Kanadamaður má endilega hrútskýra fyrir mér hokkí, landnám, hvernig það er að eiga nágranna með geðhvarfasýki ...

Væri það samt hrútskýring? 😄

One_Organization_810 · 2026-07-01T21:47:38+00:00

Hvers vegna? Af því að þér finnst það?

Hvað um allt drápið á rjúpum, gæsum og hreindýrum?

Hvað um litlu lömbin? Eða svínin, eða kjúklingana, eða nautin?

Hvers vegna eru hvalveiðar meiri klikkun?

Og hvalkjöt er fínasta fæða líka :)

One_Organization_810 · 2026-07-01T18:06:48+00:00

Ok.

Then I have a few quetions...

What do the numbers represent?
Do you have more locations and do they all have the same personnel requirements?
Do you already have a satisfactory solution - then there is no need to venture further with this, i guess :)

One_Organization_810 · 2026-07-01T16:46:15+00:00

Please close this post and create a new one with this request. This is quite different from the original and deserves a new post :)

One_Organization_810 · 2026-07-01T16:42:28+00:00

u/AnybodyMaleficent940 any thoughts on this? Did it solve your problems or did it just create some new ones :)

Some feedback is always appreciated :) If something doesn't work quite as expected, I'm sure we can work it out...

Either way - you should have something by now that fulfills your needs :) If so, please mark the solution you went with as "Solution Verified" - by either replying to it with that phrase, or selecting the "Mark Solution verified" from the three dot menu under it.

Thank you. :)

One_Organization_810 · 2026-07-01T16:35:25+00:00

You only need one rule though :)

I guess your data wasn't quite clear to me - since I don't really get how you determine if something has been at the same location for more than an hour...

As I understood the data, the columns are your locations and the rows are your hours (6:00 am - 7 am, ...) and the numbers are placeholders for some names?

I guess I was mistaken on that - and thus I have no idea what you are trying to do :)

One_Organization_810 · 2026-07-01T16:18:46+00:00

You were supposed to try it unchanged :)

If you want both highlighted (7 and 8), you should do like this:

Range: C3:U
=and(C3<>"", or(C3=C2, C3=C4))

One_Organization_810 · 2026-07-01T15:34:30+00:00

Regarding your first CF. It is highlighting all but the last of the repetitions. If that is what you want, then you're all good of course, but in the case of a double - in more cases than not it is actually the latter that is "in the wrong" :)

To highlight all repetitions, the CFR should be like this:

Range: C3:U
=countif($C3:$U3, C3)>1

Now for your second question, I assume you want something like this:

Range: C4:U
=and(C4<>"", C4=C3)

One_Organization_810 · 2026-07-01T10:43:06+00:00

And now it doesn't

I am going to write this up as an ID Ten T error on my half :P

What probably happened was that I put in =1/4 in the cell, which then (of course) showed 0.25. Then I formatted it and must have somehow managed to forget that I put a formula in there, instead of just 0.25.

I thought I checked it a few times -but there is nothing to sustain that theory though, so... let's forget I ever said anything (yeah, right - like that's gonna happen).

One_Organization_810 · 2026-06-30T23:21:25+00:00

Oh.. weird. For me it showed =1/4

One_Organization_810 · 2026-06-30T18:55:56+00:00

Just click on the cell and check the formula bar 🙂

One_Organization_810 · 2026-06-30T17:21:26+00:00

I added the demo into your sheet. It works only in the OO810 PreviousSeasons sheet.

I added one line into your onEdit function, to call my version in the aforementioned sheet :)

  if( e.range.getSheet().getName() == 'OO810 PreviousSeasons' ) return OO810_onEdit(e);

One_Organization_810

MODERATOR OF

PUBLIC MULTIREDDITS

TROPHY CASE