How to Pull Data from Another Sheet based on Criteria in Excel & Google Sheets?

  Рет қаралды 905,600

Learn Google Sheets & Excel Spreadsheets

Learn Google Sheets & Excel Spreadsheets

3 жыл бұрын

Learn how to pull data from another worksheet (aka sheet, tab) based on criteria in Excel & Google Sheets.
Sign up to get updates, practice files and code snippets eepurl.com/hwyGg1
#excel #googlesheets

Пікірлер: 319
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Recent videos: IF Functions from Hell in Excel & Google Sheets - Formulas kzfaq.info/get/bejne/bMmnjdJ8ycDMaWg.html Excel - Create PDF Files from a Spreadsheet - kzfaq.info/get/bejne/m82fjLlpuNCrmmg.html Google Sheets - Share & Work on Spreadsheets kzfaq.info/get/bejne/btqkpa2arrKVcac.html Excel Online Link to Other Workbooks - kzfaq.info/get/bejne/n9ybf9l0qrC8cp8.html
@fittolast2647
@fittolast2647 2 жыл бұрын
Thanks so much for your videos. You explain complex matters really easily, and I'm finding the info incredibly helpful!
@JohnDeir
@JohnDeir 3 жыл бұрын
Good Tutorial and I use filter function a lot. And I learned to add a second filter function to wrap the first filter function to pick the columns I only needed. And on one of my sheets I made a relationship of checkboxes to choose the columns I wanted. I find the filter function to have a lot of uses. Thanks for all the tutorials!!!
@kmj217
@kmj217 Ай бұрын
That's what I was looking for when I came across this tutorial.
@ryderoreilly9807
@ryderoreilly9807 7 ай бұрын
Perfect - I've been searching for a formula for the last 3 hours!!! and this works!!
@johnwjohnson
@johnwjohnson 11 ай бұрын
Thank you for posting this. I was at a loss as I'm creating a CRM using Google Sheets and this helped me tremendously with this issue. No other video I came across had this exact issue!
@rulouri
@rulouri Жыл бұрын
Thanks!!! I was struggling like mad since yesterday night to come up with a solution for this. I am entering data into a sheet that sums up the company's total expenses, but the expenses are made based on multiple projects. First I used filters, but this gets pretty daunting as the data multiplies. I was looking for a way to split the information according to each project in different tabs so I can see the expenses for each without having manually filter, or risk losing data while using copy-paste each time I have an entry. Your explanations are short, easy to follow, and very clear. Thank you!
@tff360
@tff360 4 ай бұрын
Quick and easy to follow explanation! I was searching and kept coming up with other solutions that didn't meet my needs. Yours did thank you!
@faithfulfungi3545
@faithfulfungi3545 Жыл бұрын
You just saved me at leasr 5 hours of manual work. Thank you so much!!
@liliboggess506
@liliboggess506 9 ай бұрын
Thank you so much! This was so helpful to help me sort commission rule data and customize it to the type of sale my employees did! I am forever grateful!
@ivanurbina2422
@ivanurbina2422 Жыл бұрын
Love it. Another feature I just learned existed. This works for the project I have in mind. Thank you so much!!!
@MiroCro11
@MiroCro11 Жыл бұрын
This was very simple and useful. Thank you!
@sanjaybiswas7926
@sanjaybiswas7926 2 жыл бұрын
You have added much value. Thank you!
@rhettdopwell5351
@rhettdopwell5351 8 ай бұрын
Thank you very much. You helped me to succeed with my project in about 1 minute of your video. Awesome.
@QuadDrums
@QuadDrums 3 жыл бұрын
Great content as always man!
@sudoalex
@sudoalex 3 жыл бұрын
Thanks to your previous video about filter functon I was able to make a huge filter thing and my favorite thing is that you can combine different filters putting them all into an array like ={filter(A2:B30,A2:A30="male"),filter(G2:L30,G2:G30>30)} and even sort that range from columns It's insanely useful!
@Maestr055
@Maestr055 2 жыл бұрын
You could even use the Query function for that, but yes, the filter function is insanely powerful!
@abdullamasud4278
@abdullamasud4278 Жыл бұрын
oh? how can you sort? When I try doing sort by converting the header into filter (from Data>Create a filter), it doesn't work :\. Can you suggest where I should look at?
@sudoalex
@sudoalex Жыл бұрын
@@abdullamasud4278 I meant with the sort() formula (Using Google Sheets)
@sudoalex
@sudoalex Жыл бұрын
@@Maestr055 I really don't like the query formula, maybe I don't fully understand it lol Tho I know SQL this one is weird
@siddharthjain6629
@siddharthjain6629 3 жыл бұрын
It was basic video. I have seen a seprate video of filter function on youtube it was a great video. You are doing great job. Keep doing it is sirji. I appreciate and admire your work
@christypropst5081
@christypropst5081 2 жыл бұрын
Thank you! This was the best and helped me so much. I was able to link data sheets for easy editing and viewing for my staff.
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Be careful with editing part. Data should not be edited in the results sheet.
@deinesrojas1077
@deinesrojas1077 2 жыл бұрын
You have no idea how much you just helped me! Thanks for the video.
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Glad to hear!
@wumbo2421
@wumbo2421 2 жыл бұрын
This is what I need now, this is super helpful. Thank you, glad to find your video.
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Great to hear!
@zacharyhill6379
@zacharyhill6379 2 жыл бұрын
you're a hero! thank you for this video
@InsightKnowlege
@InsightKnowlege Жыл бұрын
Hi mate. You’re videos are great. Easy to understand and very comprehensive. You just earned a subscriber. 🎉 I am looking on how to map specific columns in two separate sheets. So if you have any video on this please share a link otherwise would appreciate if you create a video on this topic. Thanks once again and keep it up. Ta
@sallyboustani4765
@sallyboustani4765 2 жыл бұрын
omg you just saved me so much time!thank u
@barbarapintoduarte5301
@barbarapintoduarte5301 2 жыл бұрын
I was looking for that!!!! Thanks!!!
@louisbanks2865
@louisbanks2865 Жыл бұрын
Hello! Does the filter function work where it is filtering text that is based on a formula? For example, I am looking to pick up all cells that are scored higher than 12, but the cells within the array are based on returns from an xlookup formula across 6 other spreadsheets. I hope this makes sense!
@eGlamorous1
@eGlamorous1 Жыл бұрын
You're amazing! Thank you.
@vivekasharma3011
@vivekasharma3011 3 жыл бұрын
Great video......as you create always.
@franciscobahamondes1313
@franciscobahamondes1313 3 жыл бұрын
Great Sir your content is an absolute masterpiece! Thank you very much for sharing
@sunilsirvi9072
@sunilsirvi9072 Жыл бұрын
Thanks 🙏👍 for video,I have used index,vlookup and many other methods,but thanks God,filter is easiest to use,thank u so much,I took me almost 10 days to lookup this video and found what I'm searching for,thanks once again.
@ExcelGoogleSheets
@ExcelGoogleSheets Жыл бұрын
Great to hear! Welcome to the channel👍
@KMKM2002
@KMKM2002 Жыл бұрын
Thanks a lot. On the drop down if you wanted to pull down more than 1 field, say 2 fields. Say male and female, is there a way to do that? Thanks
@SajiaSultana-mc6qi
@SajiaSultana-mc6qi Жыл бұрын
thank you so much ,its really very much helpful
@trinaharris9915
@trinaharris9915 2 жыл бұрын
Great video; I have a similar need and having issues. I want to compare names from from sheet and where the names matches, add the data for the phone number in a cell . Any suggestion on what option I should use?
@harshilshah6272
@harshilshah6272 Жыл бұрын
Very helpful... Thank you!!
@tasmks1
@tasmks1 2 жыл бұрын
is there a code you could use on Google sheets that could make the data you pull from the customers sheet modifyable and also reflect the changes on the customer tab and data pull tab?
@minkhantkoko4694
@minkhantkoko4694 2 жыл бұрын
very concise and useful
@heraldmind7620
@heraldmind7620 2 жыл бұрын
great video thank you so much.
@jackson35bmx
@jackson35bmx 3 жыл бұрын
Sou super grato ao canal! Queria saber como faço uma ordenação que parece ser básica mas não é lol. Em outro momento eu digo qual é... Thank you for vídeo!!!!
@mrs_varon
@mrs_varon Жыл бұрын
Thank You very much kind sir! ❤️
@stepansargsyan7192
@stepansargsyan7192 2 жыл бұрын
Thank you so much, this video helped us to save a lot of time :) Just one question what if we need to populate the cells with a similar word not exactly the same?
@plasticman4929
@plasticman4929 Жыл бұрын
Terima kasih .. memang tebaik bossku!
@CoachHomeUK
@CoachHomeUK Жыл бұрын
I have a sheet that brings whole rows over from another sheet depending on a status of a dropdown like your male, or female example. can I change the status of that dropdown in the new sheet? ie change male to female in the new sheet and it disappears while male is selected but is on the list when female is selected? At the moment the first entry has the formula in it so I can't change it.
@vivekkumarsingh8795
@vivekkumarsingh8795 3 жыл бұрын
Great content bro
@christine_notchristina
@christine_notchristina Жыл бұрын
THANK YOU YOU SAVED MY JOB
@ExcelGoogleSheets
@ExcelGoogleSheets Жыл бұрын
Great!
@kashifqasim8069
@kashifqasim8069 Жыл бұрын
It was very helpful I tried it work
@1anfinity08
@1anfinity08 11 ай бұрын
Hello just a question. How do you do it if you want to filter a column of numbers instead of a string
@bryanm00
@bryanm00 2 жыл бұрын
Gold, thank you so much
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
:)
@hanym.g4462
@hanym.g4462 Жыл бұрын
Super, thanks 👍
@shivraz245
@shivraz245 3 жыл бұрын
Great content. I have a question. While using the Query function, I have added data from different Google sheets through Importance and combined into one. The sheet is not moving data where special characters are used like / +. Please advise how can I include that data too.
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Use filter function instead of query.
@emilianoreyes
@emilianoreyes 3 жыл бұрын
Thanks!
@calladdicts2015
@calladdicts2015 2 жыл бұрын
Need your help, don't know if you can. We have a main tab with data of leads. Theres a column:L - with URLS, and Column:M - with Empty space for Emails to go. The 2nd tab is where our VA found SOME of the emails for the URLS in the main tab. So right now in the 2nd Tab it looks like this: Column:L - Same URLs from Main tab, and Column:M - Emails found for some of the URLS. Question: How do we get the emails that were found for SOME of the URLS to move over to the main tab Column:M but match the CORRECT URLs even though the URL's are all mixed up on the main tab Column:L??? Thank you!
@saurabhgupta4670
@saurabhgupta4670 2 жыл бұрын
thanks, its really work
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
great!
@monarca2846
@monarca2846 2 жыл бұрын
Hi, please is there a native formula or a way for cell A1 to always contain the active row?
@Maestr055
@Maestr055 2 жыл бұрын
Very nice! I didn't know Excel has a filter formula now! Anyway, there's a way to use the filter for parts of words (where H2 is the cell where you type what it needs to filter on): =FILTER(A:B,NOT(ISERROR(SEARCH(H2,A:A)))) Also, there is a way to not have the filter statements be "And" statements, so you can filter on two separate things (where H2 and H3 have search words to filter on): =FILTER(A:B,(NOT(ISERROR(SEARCH(H2,A:A)))) + (NOT(ISERROR(SEARCH(H3,A:A)))))
@bagasadifirdaus9278
@bagasadifirdaus9278 2 жыл бұрын
thank you bro, help a lot!
@destinymalone1095
@destinymalone1095 Жыл бұрын
You are a LIFESAVER thank you!
@pbower4378
@pbower4378 4 ай бұрын
Is there a way to do this with a living document? Meaning that the filters will still work etc as you add rows and more info into the sheet?
@AK-ki5kz
@AK-ki5kz 2 жыл бұрын
Thank you for this. If I am using excel to keep track of my "open" work tasks and I have a tab for each segment of work and within each tab I have tasks that are "OPEN". How do I get one excel sheet to display the Open from several excel sheets?
@ratnadeepvaidya8509
@ratnadeepvaidya8509 2 жыл бұрын
Thank ❤ you so much for this..
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
:)
@abdullahalmamun8264
@abdullahalmamun8264 Жыл бұрын
Man! Damn! this is a life saver.
@ExcelGoogleSheets
@ExcelGoogleSheets Жыл бұрын
👍
@jackoneill2321
@jackoneill2321 2 жыл бұрын
This is a great video, thank you! I am wondering, can you use this filter function to pull information from multiple worksheets? So if you had another sheet with customers in, could you pull based on criteria from more than one sheet (with the same criteria) into the data pull sheet?
@41nishant
@41nishant 2 жыл бұрын
i had same query, did you find any solution for the same. Also what i did to solve my problem temporarily is that i placed the first pull out from column A to Column F representing sheet 1 and then the second pull out from column H to column M representing sheet 2, deliberately left column G blank for spacing.
@indycog
@indycog Жыл бұрын
Also looking for this.
@DaisoOne
@DaisoOne Жыл бұрын
I was wondering if this would work if I had a timestamp column and it’s in this format “4/1/23 14:00” could I put my criteria at 4/1/23 to pull all data regardless of the the time .. or would I have to separate it ? Because my software exports the cvs file in this format..
@hashimbantan9096
@hashimbantan9096 Жыл бұрын
THANK YOU SO MUCH !!!!
@ExcelGoogleSheets
@ExcelGoogleSheets Жыл бұрын
👍
@angelotelemaque6698
@angelotelemaque6698 5 ай бұрын
Thanks for this videos it is a great help, but why are some of the columns being left out when I do it. Is there a certain amount of columns that it can take?
@saifjawaad
@saifjawaad Жыл бұрын
Thank you for this video. I have an additional question. If I make another entry in the "customer" sheet, how can this entry be updated in the "data pool" sheet? Please help. Thank you
@simaofrazao2140
@simaofrazao2140 4 ай бұрын
you saved me
@mohammadiqlas73
@mohammadiqlas73 2 жыл бұрын
Thanks a lot
@user-vy5bl6cd2m
@user-vy5bl6cd2m 7 ай бұрын
Is there a way to have a "Show All" selection in the filter list (Excel)? In other words be able to use the drop down to show male or female or all.
@RaviKumar-pr2by
@RaviKumar-pr2by 10 ай бұрын
if I do have ms-office 2013 then what should i do filter out the same by like by employee name or Dept.. what type of formula i should apply..
@wsbarth92
@wsbarth92 2 жыл бұрын
Is there a way to 'link to the source data' on whatever 'main data set' the filter was taken from?
@jasonhebard5885
@jasonhebard5885 Жыл бұрын
Great video! Happy to find the Filter function. When applying the IF statement to my filter, I receive an error “you’ve entreated too many arguments for this function.” Any assistance would be great to eliminate the zeros. Thanks!
@anmolparasar
@anmolparasar Жыл бұрын
Sir I need your help little bit I have created a mail template using vba and shared my all staff and created UI FOR SENDING THE MAIL BY ANY ONE SO ISSUE IS LAST SIGNATURE IMPRESSION IF I SEND THE MAIL THEN OK I SET THE SIGNATURE BUT IF OTHER STAFF CLICK ON THAT UI BUTTON THEN MAIL WILL BE SEND BUT BY MY SIGNATURE SO HOW IT CAN IMPROVE
@davidtitz2346
@davidtitz2346 14 күн бұрын
Would filter formula work if I wanna pull data from multiple tabs?
@MD-cu8tt
@MD-cu8tt 2 жыл бұрын
Thank you !
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
:)
@emzedes
@emzedes 2 жыл бұрын
Great video, thanks for the upload! Further extending this scenario, is there a way to automate this process so that when a new entry is created in the masterfile, it will automatically pull the data and display it in the relevant worksheet? [e.g if a new female customer is added to the database, google sheets will automatically copy their data into the worksheet for female customers?]
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
This should do in Google Sheets kzfaq.info/get/bejne/h7qphtqmzdasd2Q.html
@tsokobersta4499
@tsokobersta4499 9 ай бұрын
Is there a way that the resulting output will be only specific columns that I want from the array?
@KevinMarr-ko4nu
@KevinMarr-ko4nu 29 күн бұрын
Can you filter it it pull the data based on multiple responses from the same column ie: by state: KS, CA & TX.
@arnellcarmichael635
@arnellcarmichael635 2 жыл бұрын
That 'Filter' formula was clutch! The first few minutes of seeing it, I was able to modify it to my needs. However, when I pull the requested data from my Master sheet into another workbook, I cannot double click into the cell without the formula giving me an error.
@shubh7435
@shubh7435 3 жыл бұрын
Sir: I want to know how can I get an output=input/4.5 as per input without using any extra cells or columns. Suppose I typed 450 in one cell of the particular column and it automatically puts 100.
@tinkusharma9129
@tinkusharma9129 2 жыл бұрын
Great 👌👌
@stewartw7302
@stewartw7302 Ай бұрын
I have a compex excel workbook to log all my vegetables. I want to pull certain selected data from the main tab called 'VegetableList' into another two tabs called 'sowinglist' and 'orderlist'. I have identified the vegatable I want in the second tab by an 's' for sowing and 'o' for ordering in a column in the main tab. i have meticulously followed the formula as instructed substituting customers for vegetablelist and identifying the correct range of cells, etc. but it keeps saying furmula invalid. HELP!
@schuylersheaffer2448
@schuylersheaffer2448 2 жыл бұрын
Fantastic video that answered a question I had searched all day for. One additional question regarding my "data pull" tab: I want to add additional columns on that tab and provide more data and keep that data tied to the row that it's in. Say i added something in in the data pull tab in cell L6, information for Donna Garcia. Then on the native "customers" tab I change Monica from Male to female, or delete her entry. When I do that everything shifts up and the data i entered for Donna is now in the same row as Crystal. Is there a way to tie that info to Donna?
@lauraxirum5945
@lauraxirum5945 Жыл бұрын
Did you ever get an answer to this?
@bryanlouiemaghanoy106
@bryanlouiemaghanoy106 2 жыл бұрын
Glad to see this tutorial. It helps me a lot in reporting. Is there a way to have multiple filters, e.g. I want to filter two items from same column?
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Yes. Watch FILTER function video on the channel for more information.
@altafmubarak
@altafmubarak Жыл бұрын
can we also change the column while it get filter from the original data? i.e: original data has some figure on column "H" but while getting the data I needed that "h" data to be shown in column "g" ......is it possible ? please help
@fosterian32
@fosterian32 2 жыл бұрын
Hi there, thanks for this super helpful video. Is it possible to do a filter in Google Sheets on a partial text criteria?
@levicruz2609
@levicruz2609 2 ай бұрын
If you added a female customer in your master list will it update the items in the data pull tab?
@MrShibnath82
@MrShibnath82 2 жыл бұрын
Nice!! Thanks mate. :-)
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Glad it was helpful!
@mslilwolfzpina9539
@mslilwolfzpina9539 7 ай бұрын
yeaaaaaa boiiiii your the besttttttt !!!!! fireeee wooo woooooo!!
@unclemoo
@unclemoo 2 жыл бұрын
Great video! My only issue is not being able to sort. Data is pulled in properly, but I am not able to edit any cells without losing the whole table. Sorting shifts some data down a few rows. help!
@user-uq4gq8gy9r
@user-uq4gq8gy9r Жыл бұрын
If the file size gets too large with the number of rows in my data being pulled with Filter function, how do i control that esp when I have to house my two connected files on Sharepoint?
@jakedesjardins9146
@jakedesjardins9146 2 жыл бұрын
How do I use this formula but instead of male or female, I want to pull line items from a spread sheet if the value is equal to or greater than 1?
@sreejachatterjee8063
@sreejachatterjee8063 10 ай бұрын
I want to pull the data if the column has any value in it and not blank. Will the same formula work?
@mariaalejandrahongbeirute878
@mariaalejandrahongbeirute878 Жыл бұрын
great explanation, I only have one little question; could you sub filter this by specific Zip code numbers with a drop down list?
@ExcelGoogleSheets
@ExcelGoogleSheets Жыл бұрын
sure
@analiane1
@analiane1 Жыл бұрын
Is there a way to input sheet name from a dropdown list of sheet names?
@juliorivera1957
@juliorivera1957 Жыл бұрын
Can data be pulled not from only one sheet to another, but from many as well. Let us say I have sheets from January to December, and wish to pull certain data from each one of them to a new one, is it feasible using this one formula you just showed ??
@timrichardson518
@timrichardson518 Жыл бұрын
slick 6💯
@woundedhealer8575
@woundedhealer8575 10 ай бұрын
Can this same process work if I have multiple different files, not just sheets?
@ah000
@ah000 Жыл бұрын
Worked! However, if you try to edit anything in the new list that was pulled, the whole data will erase and shows up as "REF" error
@Ymahtani
@Ymahtani Жыл бұрын
How do you ensure that you pull certain headings from previous sheet?
@aaronpatricktalubo8400
@aaronpatricktalubo8400 11 ай бұрын
How can i replace or fill blank cells with zero in pivot table? Thank you for your answer
@albamazariegos4785
@albamazariegos4785 5 ай бұрын
Will this function work if new data is entered and will it move it automatically?
@arjunrathod3614
@arjunrathod3614 2 жыл бұрын
Hi! Thanks for this video. Just I have one query. If I have to pull Data from Another Sheet based on a Criteria and I want that this new created sheet automatically get updated based on same criteria if worked on 1st sheet. How to do that. I do hope I will get answer. Thanks again.
@MrNaturalcure
@MrNaturalcure 2 жыл бұрын
IF select from drop down a text "abc" then what ever I write on the same sheet should also write same in real time on the other sheet , and if I select "def" text from drop down then it should be written on the DEF sheet please guide, Thanks in advance
@795mehran
@795mehran 3 жыл бұрын
Amazing. What if we have a date column in data and we want to filter the data by changing date in reference cells?
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Use a cell like I did with numbers.
EASILY Make an Automated Data Entry Form in Excel
14:52
Kenji Explains
Рет қаралды 364 М.
Stop using VLOOKUP in Excel. Switch to INDEX MATCH
11:05
Excel Level Up
Рет қаралды 2,3 МЛН
THEY made a RAINBOW M&M 🤩😳 LeoNata family #shorts
00:49
LeoNata Family
Рет қаралды 31 МЛН
Heartwarming: Stranger Saves Puppy from Hot Car #shorts
00:22
Fabiosa Best Lifehacks
Рет қаралды 20 МЛН
The child was abused by the clown#Short #Officer Rabbit #angel
00:55
兔子警官
Рет қаралды 24 МЛН
Was ist im Eis versteckt? 🧊 Coole Winter-Gadgets von Amazon
00:37
SMOL German
Рет қаралды 35 МЛН
Google Sheets - QUERY from Another Sheet, IMPORTRANGE, Use Multiple Tabs, Subquery Examples Tutorial
24:51
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 445 М.
How to Link Data Between Sheets and Spreadsheets in Google Sheets
11:33
3 Essential Excel skills for the data analyst
18:02
Access Analytic
Рет қаралды 1,5 МЛН
10 Excel Formulas That Will Set You Apart (Cheat Sheet)
18:04
MyOnlineTrainingHub
Рет қаралды 210 М.
Google Sheets Import Range | Multiple Sheets | Import Data | With Query Function
10:36
EASILY Combine Multiple Excel Sheets Into One With This Trick
8:48
Kenji Explains
Рет қаралды 252 М.
Extract Data to Separate Sheets the Right Way!
8:34
MyOnlineTrainingHub
Рет қаралды 161 М.
THEY made a RAINBOW M&M 🤩😳 LeoNata family #shorts
00:49
LeoNata Family
Рет қаралды 31 МЛН