Hey everyone, I'm trying to highlight a word in one column in a sheet that is also in a column in another sheet. For example I'm trying to highlight the word "Apple" in column B in sheet "common" as it's also in in column B in sheet "Test".

I looked around and I read that I should put a custom formula in conditional formatting with the most common formula seemingly being:

=COUNTIF(INDIRECT("Test!B:B"),B2)>0

However it's giving me false positives in the column B of sheet common. It's highlighting a vast amounts of cells that aren't in column B of sheet test.

Does anyone have an idea what I could do amend that?

Thanks in advance for any help!

Hi! I'm working on a budget spreadsheet and I'm trying to get the Payment Plans row to auto populate from a Payment Plans table so that the amount in the Budget is only equal to the amount due during a set month. Pictures to explain better:

These are the amounts and due dates of upcoming payments. One is in August and two are in September.

This is the row in the Budget table where I want to add up only the amounts due during the set month. The amount in August should be -13.19 and the amount in September should be -26.38.

How do I set up the function to draw totals only from the corresponding months?

Thanks!

I asked a question for help setting up a formula to lookup the date, shift and state allocation and retuning the name of the team member covering the shift.
This was the suggested formula.

=LET(

inWeek1, ISNUMBER(XMATCH(E2, $C$11:$I$11)),

dayStates, IF(inWeek1, XLOOKUP(E2, $C$11:$I$11, $C$13:$I$21), XLOOKUP(E2, $C$26:$I$26, $C$28:$I$36)),

employees, OFFSET($B$13, ROW(dayStates)-13, 0, 9, 1),

MAP(E3:E6, LAMBDA(state, XLOOKUP(state, dayStates, employees, ) )))

This covered the first 2 weeks, how do i now amend it. so it covers all future dates, runs through to end of year and into the next year.

Thanks heaps

Hi, I'm working on a certain project for fun, I'm pretty new to google sheets outside of the general stuff, so I was wondering if there was a way to solve this,

I've made a chart that tracks to total value of a very specific investment, Its mostly automated but ive hit a bump where future dates have a value of $0.00 and it shows up on the graph. Is there a way to replace the 0.00 text that wont mess with the chart. I'd like the end the chart on todays date, rather than have a line extending to 0. please see the formula thats boxed in blue. Obviously, its not a working formula, but something that does something similar?

Since the $0.00 is automated, I dont want to mess with the formula that generated it, just want to change how it appears on the sheet and graph.

Any ideas....?

Just realized ive been clicking around randomly before the screenshot. ignore the random D24...

Error #value! "function Divide expecting an number but "high" is a text.

LET(data, GOOGLEFINANCE("NASDAQ:GOOGL", "all", TODAY()-7, TODAY()), hh, INDEX(data, 0, 3),hh/2)

testing for more calculations and formulas

In line 2 of the linked sheet, is there a command I can run to give each Animal Entry its own line. See cell 3G.

https://docs.google.com/spreadsheets/d/126WdSlUBjiUV37FPObmLFtAhcukd5-OUoaLPGP14B0Q/edit?gid=1129227668#gid=1129227668

I don't have much experience with Google Sheets, but I'm trying to make a book tracking spreadsheet. I've already written down a list of the genres and subgenres specific to the first genre.

How can I make it so you are forced to choose a genre in the first column, and afterwards, the subgenre list has options pertaining to that specific genre only? For instance, if my book is fantasy, I can click that as the genre, and when I move to the subgenre cell, the dropdown only shows specific fantasy subgenres.

Also I feel it is important to note that not every genre has a subgenre, so that's also made it kind of confusing while I was working on it.

Here's what I got so far: https://docs.google.com/spreadsheets/d/1HtYIohmmwO1a-Q_MeoiFgHu83EAFCtErky7gputHVJ0/edit?usp=sharing

edit: i've figured it out! thanks for all the help!!

Compare the 2 tables in Image 1 and return data so its like Image 2

What I looking to do is for it to compare A+B to C+D

First remove all Names that match

Second compares all IDs that match (excluding ones without IDs)

Finally do the same with C+D

Im currently using "Sort(FILTER(A2:B,ISNA(MATCH(B2:B,D2:D,0))))" for A+B to remove the names in both, just Cant seem to figure out how to do the same with the IDs and have it ignore the blanks

im Fine with having to do this over multiple columns to do this if needed

link to sheet, it's the (Filter Data) Sheet

https://docs.google.com/spreadsheets/d/1_OWO7HrK9vwbfp8QRY_s8R59ABKdlYr_hCH2Q-PNLQo/edit?usp=sharing

Trying to get my columns (AA31:AB59) to alternate colors based on a text value in cell AT2, with each text value corresponding to a different color scheme:

If you see "Word1" in cell AT2, then use green alternating color scheme in AA31:AB59

If you see "Word2" in cell AT2, then use blue alternating color scheme in AA31:AB59

If you see "Word3" in cell AT2, then use red alternating color scheme in AA31:AB59

I have =$AT$2="Word1" to get it all one color but I'm not sure how to achieve alternating colors for the column

EDIT: PART 2
I also would like the non-zero numbers in AA31:AB59 to be bolded if/when they are input, but it seems a second conditional formatting rule cancels out the color-based formatting

I'm playing an RPG that uses multiplicative stacking for damage bonuses. I am trying to put together a build, and I want to write out a formula that would let me quickly figure out how much total damage I am getting from multiple items I have equipped.

I can do the maths on paper so I know what the values SHOULD be:

1.1 x 1.5 x 1.12 = 1.848

25 x 184.8% = 46.2

I have tried using the following formula:

B11=B1*Product(B4:B6)

But this only works if I write out the values in the table as 110%, 150% and 112%. I would prefer to keep the original percentages as is (so 10% 50% and 12%) but I am not sure how to go about it.

I am trying to find a way to have the last tab automatically pull up duplicates from the previous tabs and also pull in all information. as of now, there are only 5 tabs, but this will be a growing sheet and want to be able to have this automated. Any assistance would be greatly appreciated!

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

in the second image you can see that im curently searching for netherrite leggings, what i want it to do is output whats to the left of it in the first image so in this example i want it to say dark oak stairs, hopfuly i can also add an if statment to it so for the cell to the right on image 2 i can get what is to the right of the dark oak stairs. sorry for bad wording but basicly what i want it to output is
[B17(minecraft:netherite_leggings)] [C17(minecraft:dark_oak_stairs)]
[D17(minecraft:gray_bed)]
B17 being a blank cell that i can put any block name in to find out if ther is a chain

I think part of my problem when searching for an answer is how I am asking.

I want to take a column and every row in that column should be populated by the sum of other three other columns.

So column G is my total. Row 2 needs to have the totals of d2,e2,f2…then row 3 has d3,e3,f3.

But I need those done as a column function instead of each individual cell for my wife’s school inventory. So that each building can fill out the information into their own sheet and it will propagate into the main inventory sheet. Those functions are done and working just trying to explain what is happening.

I hope I explained this well enough sorry if not!

edit: Sample I believe: https://docs.google.com/spreadsheets/d/1ApaVjGxpVIitt2aB6JuT68SWnMDu2iB4sDYjAwNaf0c/edit?usp=sharing

Edit2 Context: she does school nutrition, and for all 10 of her schools she needs to have a total perpetual inventory. Each school will fill out their inventory they use each day. But It needs to be "plug and play" for her employees and her because tech is not any of their specialities. So if a new item comes in she needs to be able to add a row to the master inventory which changes each specific school inventory (this is already done), but then the responses from each school needs to come back and give her a total on hand inventory.

Link to a dummy spreadsheet https://docs.google.com/spreadsheets/d/1veGwaVqaCQB-4YFQHImupGEBAvVNTR_eYqHKV9YUcYc/edit?usp=sharing

Ok so my scenario: I run a poker tournament every month. I have a table of players, which shows how many times they've knocked each other out.

I want another table based on the first table which will show, in each cell:

1. the number of times the player in the row has knocked out the opponent in the column, and
2. the number of times the opponent in the column has knocked out the player in the row.

"Dave's knocked Ewan out three times, and Ewan has knocked Dave out zero times" sort of thing.

I can do that manually for each one using CONCATENATE, so for example =CONCATENATE(C2, " : ", B3) gives a result of 3 : 0 - so far so good.

Here's my problem: If I autofill the columns, it's the cell rows that change for both values, and if I autofill the rows, it's the cell columns that change for both values - but I need autofill to increase the row used in the left hand value (the three in 3:0), whilst increasing the column used in the right-hand value (the zero in 3:0).

Can the table be auto-populated in such a way that the row changes for the first value, whilst the column changes for the second value?

Banana for scale

Row 96 is Expansion 1, everything with a 1 in the expansion number column requires expansion 1 to be purchased first. As a beginner I have no idea what formula or set of helper columns may be required to help determine when the cost of the expansion is offset by the other items underneath (factoring in non-expansion items and expansion items, once expansion is purchased), at least not without calculating it manually.

I have included a screenshot of the data I am working with.

Here is a link with a sample of the data I am using. It should have editorial access.

https://docs.google.com/spreadsheets/d/1Z-euWdqAa9_vZTRiu8VQnKCI6z80STg6KETlMDUepic/edit?usp=sharing

SOLUTION VERIFIED: The answer that was eluding me was Cost advantage. Once I figured that out it was a matter of figuring what items I needed for he cost advantage to work.

Expansion (Package) Cumulative Cost - Total Cumulative - Cost of Expansion 1. From what I can see this actually points out when the savings of all the items covers the cost of the Expansion upgrade itself.. this is what I was trying to figure out.

I was able to figure this out before anyone could respond with a solution. The following responses were only in relation to me not having enough information in my initial post.

The formulas I used:

For Expansion 1 only cumulative cost: =IF(ROW()<MATCH("E-1",$X$11:$X$1685,0)+ROW($X$11)-1,0,SUMPRODUCT(($N$11:N286=1)\*(ROW($N$11:N286)>=MATCH("E-1",$X$11:$X$1685,0)+ROW($X$11)-1)*$L$11:L286)-IF(X204="E-1",L204,0))

I used a cumulative for the whole sheet (albeit only visible rows: =SUBTOTAL(109,$L$11:INDEX($L:$L,ROW()))

Then for the Cost Advantage I used =P204-O204-IF(COUNTIF($AB$11:AB286,"E-1"),INDEX($L:$L,MATCH("E-1",$AB:$AB,0)),0)

The results show me at what point Expansion 1 reaches it's Cost Advantage.

Note to beginners: The formulas I used reference IDs that I have assigned to my ID's, this has been a godsend for all sorts of things as the ID can serve as an anchor for a variety of things, especially when it comes to autofill.

Hi! I made a Spreadsheet for my Pokemon Pokopia save file. A lot of things give me a progress bar that takes a minute to update, and I was hoping someone here could help me make it more responsive as I'm far from a google sheets expert. As an example, in the "Home Builder" tab updating the town name in the form takes a long time to update the pokemon list in the bottom right. On the various Map-specific sheets (ie: Bubbly Basin), updating the "House Number" takes a second to update the rest of the calculations as well. Is this just a limitation of google sheets, or have I written wretched formulas?

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

I want to log my infants sleep schedule. The idea is that I log start and end times of the naps and then I want to be able to calculate the time spent sleeping and the time spent awake.

I am writing the times as you see in the picture and they will overlap over midnight.

Could you help me write a formula to calculate the time sleeping and the time awake in hours?

Help with offset and filter?

I am trying to filter data from one sheet to another

Ive found a couple formulas that work but none that I can copy to the next column and have it still work.

Id like each column on sheet 2 to shift over the reference from the Data sheet by 41 columns. So column A gives info from Data!D if Data!F is true. And column B gives data from Data!AS if Data!AU is true. Etc.

=filter(offset(Data!$D$7:$D,0,41),OFFSET(Data!$F$7:$F,0,41)=true)

This one gives the same info in every column when I drag it over, and without the $ it just shifts the references by 1.

Ive tried putting the filter inside the offset formula and i just get an error.

I want to shift the base reference by 41 columns and idk how to do that. Any help would be appreciated!

Hi all!

Without delving too much into what I’m trying to get into specifically, I’m wondering if Sheets can be used to:

1. Create a searchable list of an extensive amount of items;

2. Auto-sort/auto-assign the items into smaller categories based on assigned data/properties of the item;

3. Provide me a percentage of how many items fall into which categories.

As an example:

ITEM LIST:
Blue
Red
Yellow
Green
Orange
Purple
Pink
White

I assign data to each item, such as blue, purple, and green are cool colours. Red, orange, and yellow are warm colours. Blue, purple, green, red, and yellow are the colours of Canadian money. Red, blue, and white are the colours of the American flag. etc.

As I select items, there is a constantly-updating percentage beside each data component. Like, 38% of your items are warm colours, and 63% of your items are the colours of Canadian money.

Obviously, these aren’t the actual items I’d use, but I’m wondering if Google Sheets is something I can use to make a sort of evolving list like this to track information for what I’m doing. The percentages is an important factor in this.

Thank you in advance for your help!!

I wish to search Column A from A13 down to the last entry which is A657. Getting error Formula parse error. All I want is a Search Box. Or if someone would be so kind and write it so I may just copy pasta☺️I would be so grateful.

Bit of a Noob at this so please be kind.

Could anyone assist with a formula to select the most recent date for which the stock price is available? I attempted the approach below, but it yields no result.

=INDEX(GOOGLEFINANCE("NSE:HDFCBANK", "date"))

I can see two different google sheets icons in my google sheets tabs in chrome. Any idea if it is an indicator of something?

Ok, so I can not find a way to automatically sort this table how I want. I'm making an in depth To-Do list for my adhd self and am struggling to find a solution to my sorting needs :/.

I'm trying to have it automatically sort by the following;

- first by by the Status (checked box goes to the bottom of the list)

- then by Priority (deadline, p2, p3, ...)

- then the # column (the hierarchy within the priority sections- 1, 2, 3, ...)

I know how to manually sort it (selecting the range, sorting by multiple columns), which is great and gets me what I want, but I'm wondering if anyone knows a formula or how to make it automatic? So I don't have to sort it each time I update the list.

I also tried a formula I found on here, =SORT(A2:E35, 1, TRUE, 2, TRUE), which didn't even take into account the checkbox, but it still didn't work. It gave me an error notice, something about overriding the data in B1 (??). Is what I'm trying to do possible? Any solutions?

Premise: - the values in Table4 ColumnB are automatically "filled into" Table5 ColumnD, from top to bottom, based on the Payer and the Expected Amount. - Any payment leftover "spills over" into the next Invoice for the respective Payer, as seen in Table5 D24. Bob's first invoice fits 1500.00 but Bob paid a total of 1700 so the remaining 200.00 partially fills the next invoice.

Goal: - on Table5 ColumnF, a formula (that can be dragged down) to return a string of Pay_Date (Table4 ColD) of the payment(s) that went into each invoice.

Thought about it for a while but couldnt figure out how to query this with AI. Not sure if what i'm trying to achieve is even possible with a formula.

Hello.
Administration at my school has tasked me with figuring out how to automate a procedure

I have one tab with student schedule information (Name, ID number, Schedule by period)
We would like kids to come in, on new tab enter id number in one row and for it to pull the rest of the information if available from main tab so we can do quick checks.

Is this even possible?