Forum Discussion

Noobexcel's avatar
Noobexcel
Copper Contributor
Apr 16, 2025

Populating data in a table from several cells in a different table on another sheet.

Hello Excel experts !!

I have a challenge for you.



I want to populate data in a table on the SUMMARY TABLE from the table on the sheet SWMBE entry table. (see attached)
I created a drop-down list in the SUMMARY TABLE, and I would like data to populate automatically from this list. The issue is that the data is in different cells that aren't close to each other.



The data would come from each PCO (TOTAL NON-SWMBE, TOTAL MBE, TOTAL SBE, TOTAL WBE)
I want this data to appear in the summary table when I choose a PCO in the drop-down list.


Thank you for your help !!! Much appreciated 

excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Apr 16, 2025

    Well we have a problem in that you circle 4 values in each column but have columns in the target with 1, 3, 3, and 2 copies of the values respectively and no comment how to fill that in (do you really want those values repeated those number of times?)

    As for how to do it there are various option but something like defining each row on the source from the first value to the last needed value according to the name something like: Non_SWMBE := Sheet1!E20:Z20  (NOTE you can just highlight that row of data and type that name in the box to the left of the formula entry box and hit enter.

    Then something like:

    =TRANSPOSE(CHOOSECOLS( VSTACK( Non_SWMBE, MBE, SBE, WBE ), XMATCH( A13, Sheet1!E11:Z11))

     this is guesstimating A13 to be the corresponding drop down and Sheet1!E20:Z20 to have the data and row 11 to be the corresponding headers...

    And this will only give you the 4 numbers so if you need the numbers repeated you would need to add them accordingly.

    Hope that is enough for you to run with

Resources