Hi All,
Master Data
[TABLE="width: 450"]
<colgroup><col width="140" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="108" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="120" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="71" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="2"> <col width="75" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="75" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="89" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody>[TR]
[TD="class: xl147, width: 140, bgcolor: aqua"]PRODUCT CODE[/TD]
[TD="class: xl148, width: 108, bgcolor: aqua"]Customer Code[/TD]
[TD="class: xl149, width: 120, bgcolor: aqua"]Work Centre[/TD]
[TD="class: xl150, width: 71, bgcolor: aqua"]Packs / Hour[/TD]
[TD="class: xl150, width: 71, bgcolor: aqua"]Packs / Case[/TD]
[TD="class: xl151, width: 75, bgcolor: aqua"]Cases / Hour[/TD]
[TD="class: xl149, width: 75, bgcolor: aqua"]Eff'cy %[/TD]
[TD="class: xl152, width: 89, bgcolor: aqua"]Plan Rate[/TD]
[/TR]
[TR]
[TD="class: xl153, bgcolor: white"]P CODE[/TD]
[TD="class: xl154, bgcolor: white"]CUST[/TD]
[TD="class: xl154, bgcolor: white"]W/C[/TD]
[TD="class: xl154, bgcolor: white"]PPH[/TD]
[TD="class: xl154, bgcolor: white"]PPC[/TD]
[TD="class: xl155, bgcolor: white"]RATE[/TD]
[TD="class: xl154, bgcolor: white"]EFF[/TD]
[TD="class: xl155, bgcolor: white"]PRATE[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]CQ6762[/TD]
[TD="class: xl156, bgcolor: yellow"]Ct[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,650[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]138[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]CT6762[/TD]
[TD="class: xl156, bgcolor: yellow"]Ct[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,475[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]206[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]IC10008[/TD]
[TD="class: xl156, bgcolor: yellow"]Ic[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]220[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]IC10009[/TD]
[TD="class: xl156, bgcolor: yellow"]Ic[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]220[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD00051[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]157[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl162, bgcolor: yellow"]KG10042[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10042[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl162, bgcolor: yellow"]KD10045[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10065[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10066[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD90014[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,800[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]300[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Daily Workbook
[TABLE="width: 170"]
<colgroup><col width="140" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="71" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="2"> <tbody>[TR]
[TD="class: xl146, width: 140, bgcolor: aqua"]PRODUCT CODE[/TD]
[TD="class: xl147, width: 71, bgcolor: aqua"]Packs / Hour[/TD]
[TD="class: xl147, width: 71, bgcolor: aqua"]Packs / Case[/TD]
[/TR]
[TR]
[TD="class: xl148, bgcolor: white"]P CODE[/TD]
[TD="class: xl149, bgcolor: white"]PPH[/TD]
[TD="class: xl149, bgcolor: white"]PPC[/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10042[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl153, bgcolor: yellow"]KD10045[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10065[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10066[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
Hope this explains my requirement, I just need to get started, I think if I had code to open the sheet read only and could fill in the first code efficiently, I presume it would be some sort of loop to continue the process.
Hope you can help Excel Family,
Ibbo
- I have a Master Data Table, this has a huge amount of info across 500 products
- I also have a Daily workbook that may use 50 Products and only a number of the columns of data
- I want to ensure that the data in my Daily workbook is exactly the same as Master Data Table.
- So when I open Daily Workbook I want to open the Master Data table read only as many other files may do the same thing concurrently.
- I then want to lift specific data from the Master Data table that satisfy the column headers within my daily workbook.
Master Data
[TABLE="width: 450"]
<colgroup><col width="140" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="108" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="120" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="71" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="2"> <col width="75" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="75" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="89" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody>[TR]
[TD="class: xl147, width: 140, bgcolor: aqua"]PRODUCT CODE[/TD]
[TD="class: xl148, width: 108, bgcolor: aqua"]Customer Code[/TD]
[TD="class: xl149, width: 120, bgcolor: aqua"]Work Centre[/TD]
[TD="class: xl150, width: 71, bgcolor: aqua"]Packs / Hour[/TD]
[TD="class: xl150, width: 71, bgcolor: aqua"]Packs / Case[/TD]
[TD="class: xl151, width: 75, bgcolor: aqua"]Cases / Hour[/TD]
[TD="class: xl149, width: 75, bgcolor: aqua"]Eff'cy %[/TD]
[TD="class: xl152, width: 89, bgcolor: aqua"]Plan Rate[/TD]
[/TR]
[TR]
[TD="class: xl153, bgcolor: white"]P CODE[/TD]
[TD="class: xl154, bgcolor: white"]CUST[/TD]
[TD="class: xl154, bgcolor: white"]W/C[/TD]
[TD="class: xl154, bgcolor: white"]PPH[/TD]
[TD="class: xl154, bgcolor: white"]PPC[/TD]
[TD="class: xl155, bgcolor: white"]RATE[/TD]
[TD="class: xl154, bgcolor: white"]EFF[/TD]
[TD="class: xl155, bgcolor: white"]PRATE[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]CQ6762[/TD]
[TD="class: xl156, bgcolor: yellow"]Ct[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,650[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]138[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]CT6762[/TD]
[TD="class: xl156, bgcolor: yellow"]Ct[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,475[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]206[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]IC10008[/TD]
[TD="class: xl156, bgcolor: yellow"]Ic[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]220[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]IC10009[/TD]
[TD="class: xl156, bgcolor: yellow"]Ic[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]220[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD00051[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]157[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl162, bgcolor: yellow"]KG10042[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10042[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl162, bgcolor: yellow"]KD10045[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10065[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10066[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD90014[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,800[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]300[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Daily Workbook
[TABLE="width: 170"]
<colgroup><col width="140" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="71" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="2"> <tbody>[TR]
[TD="class: xl146, width: 140, bgcolor: aqua"]PRODUCT CODE[/TD]
[TD="class: xl147, width: 71, bgcolor: aqua"]Packs / Hour[/TD]
[TD="class: xl147, width: 71, bgcolor: aqua"]Packs / Case[/TD]
[/TR]
[TR]
[TD="class: xl148, bgcolor: white"]P CODE[/TD]
[TD="class: xl149, bgcolor: white"]PPH[/TD]
[TD="class: xl149, bgcolor: white"]PPC[/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10042[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl153, bgcolor: yellow"]KD10045[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10065[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10066[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
Hope this explains my requirement, I just need to get started, I think if I had code to open the sheet read only and could fill in the first code efficiently, I presume it would be some sort of loop to continue the process.
Hope you can help Excel Family,
Ibbo