How to Pull Data from Another Sheet based on Criteria in Excel & Google Sheets?
ฝัง
- เผยแพร่เมื่อ 17 พ.ค. 2021
- 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
![[4K] TREASURE(트레저) “KING KONG” Band LIVE Concert 킹콩은 라이브를 찢어🦍 [it’s KPOP LIVE 잇츠라이브]](http://i.ytimg.com/vi/p8bLLOxPDD8/mqdefault.jpg)
Recent videos:
IF Functions from Hell in Excel & Google Sheets - Formulas th-cam.com/video/6cwZoKdZh94/w-d-xo.html
Excel - Create PDF Files from a Spreadsheet - th-cam.com/video/egoYV8SjGj4/w-d-xo.html
Google Sheets - Share & Work on Spreadsheets th-cam.com/video/8ttrJiIL1As/w-d-xo.html
Excel Online Link to Other Workbooks - th-cam.com/video/ivkLvCEJXBk/w-d-xo.html
Thanks so much for your videos. You explain complex matters really easily, and I'm finding the info incredibly helpful!
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!
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!
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!!!
That's what I was looking for when I came across this tutorial.
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!
Perfect - I've been searching for a formula for the last 3 hours!!! and this works!!
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!
You just saved me at leasr 5 hours of manual work. Thank you so much!!
Love it. Another feature I just learned existed. This works for the project I have in mind. Thank you so much!!!
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!
You could even use the Query function for that, but yes, the filter function is insanely powerful!
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?
@@abdullamasud4278 I meant with the sort() formula
(Using Google Sheets)
@@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
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
This was very simple and useful. Thank you!
Thank you very much. You helped me to succeed with my project in about 1 minute of your video. Awesome.
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
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.
Be careful with editing part. Data should not be edited in the results sheet.
You have no idea how much you just helped me! Thanks for the video.
Glad to hear!
You have added much value. Thank you!
This is what I need now, this is super helpful. Thank you, glad to find your video.
Great to hear!
Great content as always man!
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!!!!
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!
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?
Great Sir your content is an absolute masterpiece! Thank you very much for sharing
you're a hero! thank you for this video
omg you just saved me so much time!thank u
I was looking for that!!!! Thanks!!!
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?
Great video......as you create always.
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.
Great to hear! Welcome to the channel👍
You're amazing! Thank you.
very concise and useful
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
Very helpful... Thank you!!
thank you so much ,its really very much helpful
Terima kasih .. memang tebaik bossku!
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?
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)))))
thank you bro, help a lot!
You are a LIFESAVER thank you!
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?
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!
THANK YOU YOU SAVED MY JOB
Great!
great video thank you so much.
Thank You very much kind sir! ❤️
It was very helpful I tried it work
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!
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
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?
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.
Hi there, thanks for this super helpful video. Is it possible to do a filter in Google Sheets on a partial text criteria?
Hi, please is there a native formula or a way for cell A1 to always contain the active row?
Great content bro
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..
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?
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.
Also looking for this.
Super, thanks 👍
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.
Thanks!
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!
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.
Gold, thank you so much
:)
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?
Did you ever get an answer to this?
THANK YOU SO MUCH !!!!
👍
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..
Hello just a question. How do you do it if you want to filter a column of numbers instead of a string
Thanks a lot
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.
Use filter function instead of query.
thanks, its really work
great!
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?]
This should do in Google Sheets th-cam.com/video/QTySwuhpHG0/w-d-xo.html
Great 👌👌
In excel, do the formulas in the original spreadsheet copy over and does it stay as a table? Btw. This is a great solution. So easy.
Is there a way to 'link to the source data' on whatever 'main data set' the filter was taken from?
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?
Thank ❤ you so much for this..
:)
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?
Yes. Watch FILTER function video on the channel for more information.
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
you saved me
Man! Damn! this is a life saver.
👍
Thank you !
:)
Is there a way that the resulting output will be only specific columns that I want from the array?
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.
great explanation, I only have one little question; could you sub filter this by specific Zip code numbers with a drop down list?
sure
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
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.
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
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 ??
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!
Can this same process work if I have multiple different files, not just sheets?
There is a channel, excel for freelancers teaching a school management system, can you show us something similar using google sheets?
Is there a way to input sheet name from a dropdown list of sheet names?
I want to pull the data if the column has any value in it and not blank. Will the same formula work?
This is great!
what if I only want to filter and auto populate specific columns only in another worksheet?
Use arrays.
Amazing. What if we have a date column in data and we want to filter the data by changing date in reference cells?
Use a cell like I did with numbers.
yeaaaaaa boiiiii your the besttttttt !!!!! fireeee wooo woooooo!!
Thanks for this. Though I cant use this to my office excel 2019 version, I just transferred editing to google sheets.
It's available in Excel 2021 and later.
If you added a female customer in your master list will it update the items in the data pull tab?
How do you ensure that you pull certain headings from previous sheet?
Nice! Thanks for the video. This gives me hope what I am trying to do is possible. I have a list of tasks with target dates. I want to pull the information to another sheet if the target date falls between the current work week. Is that possible?
It's possible. You'll need to use some date functions to figure out the start and end date of your current work week and then use those dates to filter the data using filter function.
Will this function work if new data is entered and will it move it automatically?
Is it possible to do this with the criteria being a date range? for example: return all data from 9/23/23 - 10/5/23.
How can i replace or fill blank cells with zero in pivot table? Thank you for your answer
How to import data automatically from another sheet based on time..
Like daily attendance details