Automatically transfer data from one page in excel to another based on filter value, as well as remove when certain filter selected

Question

hi there. im really new to excel and not sure where to begin on what i am trying to accomplish.. I’m looking to have my spreadsheet automatically pull the data from “Inventory” tab into “Cell #1, Cell#2 or Cell #3” (all of which contain the same information from column A-L) tabs based on which of those 3 cells is selected from the “Location on Pad/Site” cell. I would also like it to remove the data from the tables in “Cell #1, Cell#2 or Cell #3” once I have selected anything in “Final Disposal” (contains 4 different locations) cell in the “Inventory” tab.

Answer 1

Hi Désirée

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

To do this, you can use Excel's built-in functions such as VLOOKUP and IF, combined with data validation and conditional formatting. You can also create a macro to automate the clearing of data in the tables based on the selection in the "Final Disposal" cell.

Here's a general outline of the steps you can take:

Create named ranges for the data in the "Inventory" tab. Create a data validation drop-down list in the "Location on Pad/Site" cell, referencing the named ranges. Use the VLOOKUP function to retrieve the data from the "Inventory" tab based on the selection in the "Location on Pad/Site" cell. Use the IF function to check if a specific selection is made in the "Final Disposal" cell, and if so, clear the data in the tables. Use conditional formatting to highlight the selected table based on the selection in the "Location on Pad/Site" cell.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi Anna, thank you for your input and prompt response!

unfortunately i am still quite new to excel and formatting so i am struggling to figure out how to apply what youve mentioned above to my data.. im hoping that once the sheet recognizes information that should be in Cell #1, #2 or #3 that it will duplicate this information over automatically. and then once the disposal drop down is selected that it will clear the information from the main inventory screen..

Answer 3

Sure, here is a step-by-step guide on how to achieve this in Excel:

1. Open your Excel file and go to the "Inventory" tab. 2. Select the range of data that you want to pull into Cell #1, Cell #2, or Cell #3. For example, if your data is in columns A to L, select the range A1:L100 (replace 100 with the last row number that contains data). 3. Click on the "Formulas" tab in the ribbon and select "Define Name". In the "Name Manager" window, click on "New". 4. In the "New Name" window, enter a name for the range, for example, "InventoryData1" for Cell #1. In the "Refers to" field, enter the range of data you selected in step 2, and click "OK". 5. Repeat steps 2-4 for the other two cells, using different names for the ranges, such as "InventoryData2" and "InventoryData3". 6. Go to the "Cell #1" tab and select the range of cells where you want to display the data from the "Inventory" tab. 7. Click on the "Data" tab in the ribbon and select "Data Validation". In the "Settings" tab, select "List" for "Allow", and enter the range of the data validation list. This range should include the options "Cell #1", "Cell #2", and "Cell #3". Click "OK". 8. In the cell where you want to display the data, enter the following formula: =IF('Inventory'!$B$2=$A$2,INDEX(InventoryData1,MATCH($B$2,InventoryData1[Location on Pad/Site],0),MATCH(C$1,InventoryData1[#Headers],0)),"") Replace "InventoryData1" with the name of the range you created in step 4 for Cell #1. Replace "$B$2" with the cell reference of the data validation cell on the "Cell #1" tab. Replace "C$1" with the column header of the first column where you want to display the data. Drag the formula down and across to fill the range of cells where you want to display the data. 9. Repeat steps 6-8 for the other two tabs, using the corresponding range name and formula for each tab. 10. Go back to the "Inventory" tab and select the "Final Disposal" cell. 11. Click on the "Data" tab in the ribbon and select "Data Validation". In the "Settings" tab, select "List" for "Allow", and enter the options for the different disposal locations. Click "OK". 12. Go to the "Cell #1" tab and right-click on the range of cells where you want to display the data. Select "Clear Contents" to clear the data. 13. Go to the "Visual Basic Editor" (press ALT+F11), right-click on the project name, select "Insert", and choose "Module". 14. In the new module, enter the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$4" Then Range("C4:N24"). ClearContents End If End Sub 15. Replace "$B$4" with the cell reference of the "Final Disposal" cell on the "Inventory" tab. Replace "C4:N24" with the range of cells where you want to clear the data on the "Cell #1" tab.

Repeat steps 13-14 for the other two tabs, using the corresponding cell reference and range for each tab.

That's it!

Answer 4

Hey Anna, thanks again! so much detail and help (almost too much for this newbie! lol) i can get as far as step 7/8 then i am unable to get the formula to communicate properly with my sheets.. any chance that excel has a template somewhere that i could use in place of this? i havent been able to find one online myself..