Can you review Index-Match in GS Tables? Scenario: TableA contains two columns [States] and [Capitals] TableB contains the same columns TableB[States] is a drop down list, populated from TableA[States] Requirement: Populate TableB[Capitals], based on selection in TableB[States] I would like to use Index-Match, but I am getting an ambigous result. I am able to get the correct values for the MATCH function, and INDEX functions separately, but when I combine them into a single statement, I get the entire TableB[Capitals] column populated with the result for the first row of TableB[States], regardless of which states are selected in subsequent rows. The full statement I am using is this: =index(TableB[Capitals],(match(TableA[State],TableB[State],0))) Would you have any thoughts on this?
Interesting question. I just checked a file with 10 tables. There didn't seem to be any difference in the size of the file. 43 kb with Tables, 43 kb when reverted to unformatted data. No difference in time of calculations, either. But that's just from a quick check
Hi and thank you How can I give an order to Google sheet to fill a new row when using a mobile or laptop can I get these sheet rows horizontal cell by cell
@@HashAliNZ thanks for the reply... sure it lets me add the formula into a row, but if you group by catagory... that cell becomes #REF error. There is really no point in allowing us to add in that formula if the table just thinks its a standard row. The row itself needs to become a "key row", that stays at the top or bottom of the table... and when grouping things... its needs to be smart enough to add the sums to each group. you follow me?
Yeah I follow. I get updates rolled out earlier than regular users as being part of the early release schedule. My tables has the option of adding a Totals row at the bottom that stays at the bottom. So for example, I can use =sum(Treasurer_Tasks[Monthly Cost]) which gives the totals, and then I get a + symbol on the side to add extra rows to the table without it overwriting or messing up the Totals row, and the total gets automatically updated. I just checked my other account and that has that Totals row feature too. Unfortunately, you're right that it doesn't yet have subtotals - but the way it's formatted when you group it makes me think they'll add that in eventually. In the meantime, you can add a feature request (I'll do one too!) and you've got pivot tables to group by category to get those subtotals
@@HashAliNZ Yeah, I added a pivot table off to the side to get those totals for now. I’ll keep an eye on this feed you and I started. Thanks for putting in a request for that. Let me know here if you remember when it drops for us. Thanks again for being a leading content creator on this topic
If you duplicate a sheet with a table [name], the table in the second sheet is designated [name_2] and it won't let you rename it to the same as sheet 1. You also can't change the font colour. I feel these are very basic functions so I asked Google to change them.
Hey Jess, I played around with the issue you're having. I think not renaming it to the same as in Sheet1 makes sense. Each table within the workbook needs to be uniquely identifiable so we can still use them in formulas. As for the font colour, we can't change the placeholder font colours but we can change the font colour of the inputs. It would be nice if we could - I like to have my placeholders a lighter gray, and it'd be good to be able to conditionally format them too.
From a tutorial in another channel, it seems that possibility is not available (yet), but when those types of group sum's are important, I use a Pivot Table.
I have started using, like the functionality but I've encountered some apparent bugs. I created formulas using the Table[column] references and noticed at times they are changed to absolute cell references. and when sorting that causes trouble, for example I have Table1[Amount] in a formula and mysteriously gets changed to Sheet1!$P$134 and then if I sort the table it does not change the reference to the new position of the row due to the $ before the row number. Expectation is it should not revert to cell references. Also I've had some challenges with filters, for some reason when I visit the table it shows a column filtered in a strange way and I have to reset the filter (which btw does not match what is truly filtered). Good stuff but I think it is still a little buggy.
Wow those are pretty big issues! I'll try recreate them in my sheets and report back if I can replicate the problems. Over time it will get better - they've already made one update where we can go back to the table options instead of just seeing the filter actions, so they are keeping an eye on things.
@@HashAliNZ thanks. Also there are some controls that don't seem to work when accessing from tablets or cell phones. Slicers dont work for example. I think I will have to write apps with google apps scripts. I am curious whether we can access the tables and columns with some new functions. Something like getSheetByName - I guess a getTableByName would be handy.
Yeah those would be good additions. Unfortunately we can't activate scripts from mobile devices. There are a couple ways around this, like using onEdit scripts triggered by things like checkboxes. Another is to trigger scripts using Google Form triggers. Lots of workarounds but would be nice to just be able to have it more accessible
It could be used for that kind of thing, but it doesn't have features like drag and drop chips. It also takes some effort to setup an whole workflow system. If you already have Airtable or Notion or similar thing setup, I wouldn't switch to Sheets for your business needs. Those are database systems, Sheets is still just a spreadsheet system. Hopefully Google will release their other product, Google Tables, sometime in the next couple of years that should be able to match the capabilities of Airtable but it's been a long time coming already.
Absolutely. It doesn't have quite the same features, but it works pretty well. One thing I thought I was losing my mind over was the Update button in Docs. It only shows when a change is made in the original Table, but yeah it's there.
![Microsoft Excel vs Google Sheets Tables [NEW for 2024]](http://i.ytimg.com/vi/vBp5mveYZZ4/mqdefault.jpg)
It's brand new, so let me know what questions you've got and what we should try out ↓↓↓
I was watching almost tutorials channel of Google sheets features but our Ali ' s choosing the topic and way he explained
Is really amazing 😍
Can you review Index-Match in GS Tables? Scenario:
TableA contains two columns [States] and [Capitals]
TableB contains the same columns
TableB[States] is a drop down list, populated from TableA[States]
Requirement: Populate TableB[Capitals], based on selection in TableB[States]
I would like to use Index-Match, but I am getting an ambigous result. I am able to get the correct values for the MATCH function, and INDEX functions separately, but when I combine them into a single statement, I get the entire TableB[Capitals] column populated with the result for the first row of TableB[States], regardless of which states are selected in subsequent rows.
The full statement I am using is this:
=index(TableB[Capitals],(match(TableA[State],TableB[State],0)))
Would you have any thoughts on this?
can you change the default drop down color picker or color in general so it is just white or no color to match the background?
How to sort the table based on ascending value of the stock price list
Is using the table feature increasing the size/heaviness of the file?
Interesting question. I just checked a file with 10 tables. There didn't seem to be any difference in the size of the file. 43 kb with Tables, 43 kb when reverted to unformatted data. No difference in time of calculations, either. But that's just from a quick check
Hi and thank you
How can I give an order to Google sheet to fill a new row when using a mobile or laptop can I get these sheet rows horizontal cell by cell
Is there no way to insert colum totals into these new tables?
There is now! It's been updated! Just type =sum() and it'll give you a total row. If you haven't got access to that yet, it'll be rolled out soon
@@HashAliNZ
thanks for the reply... sure it lets me add the formula into a row, but if you group by catagory... that cell becomes #REF error. There is really no point in allowing us to add in that formula if the table just thinks its a standard row. The row itself needs to become a "key row", that stays at the top or bottom of the table... and when grouping things... its needs to be smart enough to add the sums to each group. you follow me?
Yeah I follow. I get updates rolled out earlier than regular users as being part of the early release schedule. My tables has the option of adding a Totals row at the bottom that stays at the bottom. So for example, I can use
=sum(Treasurer_Tasks[Monthly Cost])
which gives the totals, and then I get a + symbol on the side to add extra rows to the table without it overwriting or messing up the Totals row, and the total gets automatically updated. I just checked my other account and that has that Totals row feature too.
Unfortunately, you're right that it doesn't yet have subtotals - but the way it's formatted when you group it makes me think they'll add that in eventually. In the meantime, you can add a feature request (I'll do one too!) and you've got pivot tables to group by category to get those subtotals
@@HashAliNZ
Yeah, I added a pivot table off to the side to get those totals for now. I’ll keep an eye on this feed you and I started. Thanks for putting in a request for that. Let me know here if you remember when it drops for us.
Thanks again for being a leading content creator on this topic
If you duplicate a sheet with a table [name], the table in the second sheet is designated [name_2] and it won't let you rename it to the same as sheet 1. You also can't change the font colour. I feel these are very basic functions so I asked Google to change them.
I'm working on a video with the current limitations of Structured Tables I've discovered so far. I'll add this to the list, making it 12. Thanks!
Hey Jess,
I played around with the issue you're having. I think not renaming it to the same as in Sheet1 makes sense. Each table within the workbook needs to be uniquely identifiable so we can still use them in formulas.
As for the font colour, we can't change the placeholder font colours but we can change the font colour of the inputs. It would be nice if we could - I like to have my placeholders a lighter gray, and it'd be good to be able to conditionally format them too.
This was super helpful, thank you
Any idea on how to get subtotal (or sum) for grouped view ?
From a tutorial in another channel, it seems that possibility is not available (yet), but when those types of group sum's are important, I use a Pivot Table.
@@thomashartmann5037
That sucks. I want to use the new tables, but like he says. there is no way to get subtatals. How did they overlook that???
How to format a table like for currency is in dollar i want yo change it to another currency
I have started using, like the functionality but I've encountered some apparent bugs. I created formulas using the Table[column] references and noticed at times they are changed to absolute cell references. and when sorting that causes trouble, for example I have Table1[Amount] in a formula and mysteriously gets changed to Sheet1!$P$134 and then if I sort the table it does not change the reference to the new position of the row due to the $ before the row number. Expectation is it should not revert to cell references. Also I've had some challenges with filters, for some reason when I visit the table it shows a column filtered in a strange way and I have to reset the filter (which btw does not match what is truly filtered). Good stuff but I think it is still a little buggy.
Wow those are pretty big issues! I'll try recreate them in my sheets and report back if I can replicate the problems. Over time it will get better - they've already made one update where we can go back to the table options instead of just seeing the filter actions, so they are keeping an eye on things.
@@HashAliNZ thanks. Also there are some controls that don't seem to work when accessing from tablets or cell phones. Slicers dont work for example. I think I will have to write apps with google apps scripts. I am curious whether we can access the tables and columns with some new functions. Something like getSheetByName - I guess a getTableByName would be handy.
Yeah those would be good additions. Unfortunately we can't activate scripts from mobile devices. There are a couple ways around this, like using onEdit scripts triggered by things like checkboxes. Another is to trigger scripts using Google Form triggers. Lots of workarounds but would be nice to just be able to have it more accessible
Can it replace Airtable?
It could be used for that kind of thing, but it doesn't have features like drag and drop chips. It also takes some effort to setup an whole workflow system. If you already have Airtable or Notion or similar thing setup, I wouldn't switch to Sheets for your business needs. Those are database systems, Sheets is still just a spreadsheet system.
Hopefully Google will release their other product, Google Tables, sometime in the next couple of years that should be able to match the capabilities of Airtable but it's been a long time coming already.
can not merge cells in it :(
I just want to create a normal table. What do I do?
Headers at the top, then put in your data. If you want it as a structured table, click somewhere in the data and go to Format > Convert to Table
Can we embed a table in a doc?
Absolutely. It doesn't have quite the same features, but it works pretty well. One thing I thought I was losing my mind over was the Update button in Docs. It only shows when a change is made in the original Table, but yeah it's there.
How to remove this back to normal
"Revert to unformatted data" in the table dropdown
Greatly
Atomic refreshing date in google sheets