Keeping comments aligned to external data XL2003

E

evans502

New Member
Joined
Oct 15, 2010
Messages
15
Hiya,

I'm trying to impliment a spreadsheet that pulls data from a finance system through MS query (Sage line 500) which will show items of a certain criteria eg. "H"

Is there a way that user comments can be added that keeps in alignment with the same data upon refresh, instead of being locked only to the cell it's been put on?

For example, if the top row was removed upon refresh (cleared on the finance system) the data would shift up but not the comments.

<TABLE style="WIDTH: 399pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=532 border=0 x:str><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6692" width=183><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 51pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: #ffff99" width=68 height=35>Inv Value</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #c1ccd9; BACKGROUND-COLOR: #ffff99" width=64>Hold</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=98>Reason</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=119>Disp Code</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 137pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=183>Comments</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: style" align=right height=17 x:num>43.36</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; FONT-WEIGHT: 700; BORDER-LEFT: windowtext; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=183 x:str=" "> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-ignore: style" align=right height=34 x:num>-863.53</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">H</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; FONT-WEIGHT: 400; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">3</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">AC</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=183 x:str="Awaiting credit for overcharge Keeley Broadstock ">Awaiting credit for overcharge</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-ignore: style" align=right height=34 x:num>-611.51</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">H</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; FONT-WEIGHT: 400; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">3</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">AC</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=183 x:str="Awaiting credit for overcharge Keeley Broadstock ">Awaiting credit for overcharge </TD></TR><TR style="HEIGHT: 63.75pt" height=85><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 63.75pt; BACKGROUND-COLOR: transparent; mso-ignore: style" align=right height=85 x:num="-11318.07">-11,318.07</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">H</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; FONT-WEIGHT: 400; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">3</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style">ID</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #c1ccd9; BORDER-LEFT: windowtext; COLOR: maroon; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=183></TD></TR></TBODY></TABLE>


Thanks again in advance! :biggrin:
Rich
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
I'd say you need to store you comments elsewhere, with some kind of unique key to associate them with individual items of data. You could align the data and comments for reporting purposes with formulas afterwards.
 
Upvote 0
Hi Glenn,

Do you mean copying new rows of data into a different tab, and using vlookup to bring those into the main sheet? Thanks I'll have to try that :)

Thanks, Rich
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,590
Messages
6,179,749
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top