Forum Discussion
Using Listboxes to Transfer Multiple Cells in Excel
There's probably something out there with a solution, but I've been looking for a while and I can't find anything helpful. Here's my problem: I want to transfer multiple cells from one Excel sheet ...
It's helpful, but I was kind of hoping that it would be simple commands such as data validation and stuff, and that I wouldn't need to use VBA.
Sure. Excel is good at pulling data from another range. But you are not telling us if the data summaries start at the same location (C3) on each non-Inventory worksheet, or if there is any data on those worksheets below the summaries. I will assume the same location, no data below, and a maximum of 12 categories (can be easily increased).
You can build a reference to a cell or range as a string, and use the INDIRECT function to evaluate it:
=IF( A2="", "", FILTER( INDIRECT("'" & A2 & "'!C3:D14"), INDIRECT("'" & A2 & "'!C3:C14")>"", "nothing" ) )(The apostrophes handle the case where the worksheet name includes a space or special character. The FILTER function prevents rows with empty cells in the non-Inventory worksheets from being included.)
See the attached workbook for the example.