Issues carrying table/data over to different sheet with matching.

Question

Hi,&nbsp;I'm having trouble figuring out how to go about transferring information into another sheet based on matching criteria.&nbsp;&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;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?&nbsp;Appreciate the help.&nbsp;

steve_sumproductcom · Answer

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.

tylrp910 · Answer

Steve_SumProductCom&nbsp;&nbsp;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.&nbsp;

steve_sumproductcom · Answer

tylrp910&nbsp;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 &gt; 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 &gt; Slicer and pick "Scheme".&nbsp;Then you can just click a button to filter it as desired.&nbsp; It might look something like this, but I've put it on the same sheet just to make it simpler to see.&nbsp;

tylrp910 · Answer

Steve_SumProductCom&nbsp;&nbsp;Ok, I was able to get the pivot table together, but it is forcing a formula in the data columns instead of just listing the issue. I think it may be due to the setup of my Set column, which describes the Set # but the number of sets at to complete for each prescribed rep, load, etc. (I posted the data below):Scheme NameSets1Rep1Load1Tempo1Rest1Reps Achieved1OBA_Strength_1_Primary150.7530x160-120s0OBA_Strength_1_Primary2380%-85%30x190-120s0OBA_Strength_1_Primary21-285%-90%20x1120-180s0OBA_Strength_1_Primary1RM Test0000OBA_Strength_1_Assisting1150.5531x190s0OBA_Strength_1_Assisting1100.6531x190s0OBA_Strength_1_Assisting000000&nbsp;As a result, I'm getting this on my pivot table:Row Labels Sum of Sets1, Count of Rep1, Count of Load1, Count of Tempo1, Count of Rest1, Sum of Reps AchievedOBA_Strength_1_Assisting233330OBA_Strength_1_Primary644440&nbsp;Is there a way to adjust this without having to # each Set as 1, 2, 3, 4, 5?

steve_sumproductcom · Answer

tylrp910&nbsp;For this purpose, you don't need to have any of your fields in the Values area of the Field List pane for the PivotTable.&nbsp;Do the following:1. Drag the fields from the Values are to the Rows area. The Values area should be empty since you don't need to calculate anything.&nbsp;2. Go to the Design tab of the ribbon, and choose Subtotals &gt; Do not show.3. Choose Grand Totals &gt; Off for Rows and Columns.4. On the PivotTable Analyze tab, click the "+/- Buttons" button to hide all the buttons.5. Adjust the alignment of the cells so it looks the way you want.&nbsp;&nbsp;Also, you can attach files to these discussions by clicking on the "Open full text editor" link.&nbsp;&nbsp;

Forum Discussion

Issues carrying table/data over to different sheet with matching.

Share

Resources