Forum Discussion

Quinten_L's avatar
Quinten_L
Copper Contributor
May 26, 2023

Transposing data from a column in one table to another on a different sheet

Hi,

 

I am having issues trying to get the formula to copy across the references in the direction I want them to.

Please could you assist.

  • I have a table ("stock") that has the names of stock items in column 2.
  • I have another table ("projects") on a different sheet (in the same book) that has jobs in rows and stock ref in columns.
  • I want to write a formula that copies the correct stock ref from "stock" into "projects" that will then auto populate when I add a new column to "projects".

My eventual aim is to write a macro that adds a new row to "stock" and a new column to "projects" for ease if we need to add a new stock item to the tracker. I want this to automatically copy the formula across so that when I type the details of the new stock item into "stock" it appears in "projects and I don't have to edit multiple tables every time.

 

There are other tables that I want to pull the data across to as well in the same manor that work in the back ground running the calculations that will be hidden when up and running.

 

Thank you for any help.

 

p.s. this is a version of the current tracker

Stock - Public version.xlsm

excel
Formulas and Functions
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    May 27, 2023

    Quinten_L 

     

    when I add a new column to "projects".

     

    As far as I know a fixed structure is easier to automate by computer rather than a table need adding new columns.

     

    I also suggest pivot table to analysis data.

    So transfer you columns to rows may be the first thing.

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 26, 2023
    I think you should look into PivotTables. All those lookups will be slow and cumbersome and PivotTable may expand the way you want. They are a little hard to learn/get used to/format to make them look the way you want, but once you get them right they work well.
    • Quinten_L's avatar
      Quinten_L
      Copper Contributor
      May 26, 2023
      I've been having a quick look at Pivot Tables and I can't see how they would help. I need the end product to have three tables that can actively have data added into them on a daily basis. From what I understand pivot tables are a convenient way to group and view data not update and maintain. Forgive me if I am misunderstanding how they work.
      • mtarler's avatar
        mtarler
        Silver Contributor
        May 26, 2023

        Maybe I was mistaken. It looks to me like the 3 'calculations' tables and the 'stock' table are all lookup values from the projects table.  So each of those tables/tab could be separate pivot tables based on the data in the projects table.

    • Quinten_L's avatar
      Quinten_L
      Copper Contributor
      May 26, 2023
      I hadn't looked into Pivot Tables before, but had thought there must be a more efficient way to run the calculations. Been self teaching as I've been going.
      Thank you for the suggestion.

Resources