Elizabethp
New Member
- Joined
- Sep 26, 2013
- Messages
- 1
I am starting to work on costing and margin analysis in apparel. I need to create a formula or macro to help pull data across multiple tabs/sheets in one workbook into certain cells of another workbook or tab (depending on what is easiest)
I'm only average in excel so I am open to input as to how to do this or how my sheets need to be set up but here is where I am:
A) 1 workbookA with around 30 -50 tabs. Each tab/sheet is the same format but specific to one product sku, labeled in cell A2. I am interested in 4 pieces of information on each tab (freight, Fob$, margin, landed cost). Right now they are in cells A5,A7,A9,A23.
* I could reformat these to be horizontal if it would make it easier. (A5, B5,E5,H5)
B) I have a separate workbookB with my summary for all styles across horizontal rows mixed with other data and notes, in addition I am manually entering all of the data from my various cost sheets in workbookA in the same row.
I am thinking there may be a way to create a summary sheet in my workbookA that would just collect data from cells A5,A7,A9,A23 from each sheet and put them in a horizontal grid. I could then copy-paste special values that data into my workbookB and create vlookups that match back to the sku number in each row of workbookB.
I was also thinking I could add all of my cost sheets as tabs in my workbookB and then have a vlookups search across all of the sheets for the matching style# and then give the corresponding cell information (for example if A3 is found on any of the tabs in column A, then return cell A7 of that sheet)
Any information would've very helpful. The amount of manual entry is extreme and it constantly needs updating due to fluctuations in costing negotiations.
Again, I am willing to house tabs in the same workbookB, as the summary rollup or reformat the cost sheets, etc.
a little formatting now will help me greatly over the next few years!
I'm only average in excel so I am open to input as to how to do this or how my sheets need to be set up but here is where I am:
A) 1 workbookA with around 30 -50 tabs. Each tab/sheet is the same format but specific to one product sku, labeled in cell A2. I am interested in 4 pieces of information on each tab (freight, Fob$, margin, landed cost). Right now they are in cells A5,A7,A9,A23.
* I could reformat these to be horizontal if it would make it easier. (A5, B5,E5,H5)
B) I have a separate workbookB with my summary for all styles across horizontal rows mixed with other data and notes, in addition I am manually entering all of the data from my various cost sheets in workbookA in the same row.
I am thinking there may be a way to create a summary sheet in my workbookA that would just collect data from cells A5,A7,A9,A23 from each sheet and put them in a horizontal grid. I could then copy-paste special values that data into my workbookB and create vlookups that match back to the sku number in each row of workbookB.
I was also thinking I could add all of my cost sheets as tabs in my workbookB and then have a vlookups search across all of the sheets for the matching style# and then give the corresponding cell information (for example if A3 is found on any of the tabs in column A, then return cell A7 of that sheet)
Any information would've very helpful. The amount of manual entry is extreme and it constantly needs updating due to fluctuations in costing negotiations.
Again, I am willing to house tabs in the same workbookB, as the summary rollup or reformat the cost sheets, etc.
a little formatting now will help me greatly over the next few years!
