Forum Discussion
tylrp910
Apr 19, 2024Copper Contributor
Issues carrying table/data over to different sheet with matching.
Hi,
I'm having trouble figuring out how to go about transferring information into another sheet based on matching criteria.
In one sheet I have columns of sets, reps, load, with the corresponding #s below (sometimes 2 or 3 rows depending on the week). My goal is to have these set/rep schemes named. That way, when I build a program on another sheet, I can simply click the name of the set scheme via a list (from data validation) and have the schemes auto-filled into the corresponding cells.
I'm unsure how to proceed with naming the data set of sets/reps/load (since there are multiple weeks to account for) or what formula to use to essentially copy and paste the data set to my preferred cells based on the matching name I choose.
For example, in Sheet 1, C3-H3 is Sets1, Reps1, Load1...Load2, C4:H6 are the prescribed numbers. How would I name this set? Then, in another sheet if I have a data-validated list cell (for the scheme), what formula would carry the information over into the new sheet?
Appreciate the help.
- I think it will be easy to do if you add a column for the name of the scheme. For example, Column I could have the name of the scheme. If you put the name on all the rows that are part of the scheme, then you can use a FILTER() formula on the other sheet to bring the data over when you pick from the drop-down.
The formula would be something like =FILTER(Sheet1!C4:I7,Sheet1!I4:I7="scheme name"). You can replace "scheme name" with the cell address of the cell that has your drop-down list.- tylrp910Copper Contributor
Filter looked like a helpful tool as I looked into it, but unfortunately, I don't have that feature with my Excel. Is there a formula combination that can be used as a workaround? I was trying to use a combination of IFERROR, INDEX, and AGGREGATE to achieve the same thing.
You might have an easier time if you use a PivotTable for this. If you can share a file that has an example of the data, it would make it easier to help.
To get started, do these steps:
1. Select a cell in your data range, then click Insert > PivotTable.
2. Insert it onto a new worksheet.
3. After inserting it, in the Field List pane, you can drag all the fields you want to see into the Rows area.
4. Then choose Insert > Slicer and pick "Scheme".Then you can just click a button to filter it as desired. It might look something like this, but I've put it on the same sheet just to make it simpler to see.