Retrieve Text from another sheet

[tmcgowan]

The title isn't very descriptive. Let me explain:

I have a tab in a workbook that is a list of comments that occur, a timeline so to speak. I then have a very large spreadsheet which breaks out individual items into some mathematical detail, and then gives a summary on the right (a comment box for that product) Is there a way to automatically allow the comments that refer to a specific object update into the large spreadsheet.

perhaps a better example
On the comments tab
Product....Month....Quarter.....Year....Comment
Apples.....Jan.........Q1...........2009....Bought 100 apples
Oranges...April.......Q2............2009....Oranges were stolen
Apples.....May........Q2...........2009.....Sold to Produce Pete
Apples.....July........Q3............2009....Prices reduced 10%
Oranges...Sept......Q3.............2009....Prices increased 20%
Oranges...Oct........Q4............2009....Sold to Concentrate Carl
Apples.....Nov........Q4............2009....Inventory Shortage

On my large file(how I would like it to look)
Product.....Forecast.......Comments
Apples.......$10,000.......Jan: Bought 100 apples
..................................May: Sold to Produce Pete
..................................July: Prices reduced 10%
..................................Nov: Inventory shortage

Oranges.....$30,000........April:Oranges were stolen
..................................Sept: Prices increased 20%
..................................Oct: Sold to Concentrate Carl



Notice that there are not necessarily the same amount of comments for each product. The Forecasted number we will treat as fixed, as this question is unrealted to that value. I have to manually make comments, as to explain some things that happen on products. If I could make note of it in one central location, and then have it flow through dynamically, that would be good. Being able to have filter the date would help as well (say >Jan AND <JULY p )<JULY)<>Again, I have many products, which would be very very time consuming to update manually for each. If you know of a way to make it dynamiclly flow, that would be great.

I do NOT have excel 2007.

Thanks for any help.

 

[SSCHLACHTER]

I'd use a pivot table. Took the data you had and did text to columns:

<TABLE style="WIDTH: 270pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=360 border=0><COLGROUP><COL style="WIDTH: 54pt" span=5 width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left width=72 height=19>Product

</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left width=72>Month</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left width=72>Quarter</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left width=72>Year</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left width=72>Comment</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Apples</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Jan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Bought 100 apples</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Oranges</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>April</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Oranges were stolen</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Apples</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>May</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Sold to Produce Pete</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Apples</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>July</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Prices reduced 10%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Oranges</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Sept</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Prices increased 20%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Oranges</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Oct</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Sold to Concentrate Carl</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>Apples

</TD><TD id=td_post_2103045 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Nov</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Q4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2009</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Inventory Shortage</TD></TR></TBODY></TABLE>
Then did insert pivot table and pulled the data you wanted:

<TABLE style="WIDTH: 196pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=261 border=0><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2912" width=91><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 5440" width=170><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; WIDTH: 68pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; HEIGHT: 14.25pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=91 height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; WIDTH: 128pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=170></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 14.25pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=left height=19>Product</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=left>Comment</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=left height=19>Apples</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Bought 100 apples</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Inventory Shortage</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Prices reduced 10%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Sold to Produce Pete</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=left height=19>Oranges</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Oranges were stolen</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Prices increased 20%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Sold to Concentrate Carl</TD></TR></TBODY></TABLE>

 

[tmcgowan]

Thanks for the response. I suppose I should give a better representation of my work, as I do not believe a Pivot Table fits my needs, althought I have difficulty doing this (hence the poor example). The large file is not just data, but rather a 'working file'. The file is a forecast file, with many moving parts, formulas, and things that need to be manipulated in various ways periodically. The forecast part goes back many periods, as it builds upon itself with growth rates. The forecast part is all complete, and just need help if its possible to pull the comments. The comments, are notes to me that help me explain different things amongst the products. My goal, is the be able to store the comments elsewhere (A new tab) so that they are kept, as opposed to just manually input and delted arbitrarily. This way, timeline is kept, instead of having to search for files from months/years ago. Does this make sense?

To recap as best I can,
Have a worksheet which keeps a list of the comments, (and the information input at the same time would be something similar to Product-Date-Comment) Then be able to pull these comments into the 'working file' that might fit a certain criteria (current calander year, current fiscal year, last 2 years, etc) that you would specify.

Thanks again for your input, but I dont think it fits my needs.