I have several (source) spreadsheets from several people that are saved in the same folder. These spreadsheets are formatted the same. I have ComboBoxes in certain cells to help keep data entered uniform for reporting purposes.
I also have a "Master Workbook" that contains a duplicate of each (Destination) spreadsheet. To update each sheet I used the Paste Link option. This works fine except it doesn't paste the contents of the ComboBoxes. Is there a way to correct this? Thanks.
I thought I might have a response by now. Am I in the correct group or should I be in the programming group for this problem? Sorry for not being patient.
"MikeG0930" wrote: > I have several (source) spreadsheets from several people that are saved in > the same folder. These spreadsheets are formatted the same. I have ComboBoxes > in certain cells to help keep data entered uniform for reporting purposes.
> I also have a "Master Workbook" that contains a duplicate of each > (Destination) spreadsheet. To update each sheet I used the Paste Link option. > This works fine except it doesn't paste the contents of the ComboBoxes. Is > there a way to correct this? Thanks.
I have been on vacation so only just had time to read your message. All you can link using paste link is one cell to another.
So, the way to achieve what you want is to have the combo box on the source sheet linked to a cell, say Z99, on the source sheet by setting its LinkedCell property. Z99 will contain the value in the combobox if it is a Control Toolbox combo or the ListIndex of the item selected from the list if it is a Forms toolbar combobox.
Then on the destination sheet you have a similar combo box linked to a cell, say Y88. And you copy Z99 from the source sheet and paste link it into Y88 on the destination sheet. Then the destination combo should show the same as the source combo. However, you would not be able to change the value of the destination sheet's combo without overwriting the formula in the linked cell - if you don't need the combo on the destination sheet to allow selection of a different item then you should disable it.
Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup
That was easy! Thank you for your help. Since I needed the "destination spreadsheet" to update any changes made to the "source spreadsheet", I used the Paste Link option which works fine now. Is there any way to get rid of the zeros in the cells that data hasn't been entered in? Can I do it by code?
"Bill Manville" wrote: > I have been on vacation so only just had time to read your message. > All you can link using paste link is one cell to another.
> So, the way to achieve what you want is to have the combo box on the > source sheet linked to a cell, say Z99, on the source sheet by setting > its LinkedCell property. Z99 will contain the value in the combobox if > it is a Control Toolbox combo or the ListIndex of the item selected > from the list if it is a Forms toolbar combobox.
> Then on the destination sheet you have a similar combo box linked to a > cell, say Y88. And you copy Z99 from the source sheet and paste link > it into Y88 on the destination sheet. Then the destination combo > should show the same as the source combo. However, you would not be > able to change the value of the destination sheet's combo without > overwriting the formula in the linked cell - if you don't need the > combo on the destination sheet to allow selection of a different item > then you should disable it.
> Bill Manville > MVP - Microsoft Excel, Oxford, England > No email replies please - respond to newsgroup
If zero would not occur in the valid data to which you are linking then you could just use a number format to suppress the appearance of zeros coming from the empty cells. Select the destination cells and give them a custom number format such as 0;-0;
Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup
|
