Forum Discussion
How to Inputing datas to a specific rows and column in a big table
ymnalya Two options for you in the attached file. Since you mention that it concerns a "big table", I would prefer to tackle this with Power Query (PQ). The result is in the green table and requires only one step after connecting to the data (the blue table), and that is to pivot by destination and source. Change the data and Refresh the query for updated results.
If you are not familiar with PQ, the link below contains good material to help you get started.
https://exceloffthegrid.com/power-query-introduction/
You can't use a standard pivot table for this as the Distribution codes are probably texts. It would require Power Pivot and a DAX measure to display texts in the value area of a pivot table.
The other option uses INDEX and MATCH functions. Note that I have used a structured table (blue) for the data, resulting in structured table references which are preferable over direct cell references. Table references will expand and contract automatically with changing sizes of the data range.
ymnalya Two options for you in the attached file. Since you mention that it concerns a "big table", I would prefer to tackle this with Power Query (PQ). The result is in the green table and requires only one step after connecting to the data (the blue table), and that is to pivot by destination and source. Change the data and Refresh the query for updated results.
If you are not familiar with PQ, the link below contains good material to help you get started.
https://exceloffthegrid.com/power-query-introduction/
You can't use a standard pivot table for this as the Distribution codes are probably texts. It would require Power Pivot and a DAX measure to display texts in the value area of a pivot table.
The other option uses INDEX and MATCH functions. Note that I have used a structured table (blue) for the data, resulting in structured table references which are preferable over direct cell references. Table references will expand and contract automatically with changing sizes of the data range.
- Hello, thank you for your answer, it helps me too.
burbigo3 Great! Glad I could help.
