Macro to Insert Specified Number of Columns

[JoselynW]

Thank you in advance.

I have a worksheet that I use to audit office locations for a specific organization. When I use it, a compnay may have 3 offices or 100 offices in the audit. I have a title page where I input data. My master file would have an additional worksheet with 1 office and all the necessary calculations. I would like to be able to input the number of offices into a cell on the title worksheet, and have it populate the proper number of columns based on the #of offices input on the title page and then copy calculations from Office #1.

Can anyone help? Please! I'm on a deadline and just can't figure it out. I really appreciate your time and assistance!

 

[lenze]

Hi and welcome to the board!!
Not really sure what you are after, but the general approach would be

Sub InsertCol()
x = InputBox("How many offices?")
Range("$B$2").Resize(1, x).EntireColumn.Insert
End Sub

You can use a Range instead of the InputBox

lenze

 

[JoselynW]

This worked perfect! You are a life saver! I am inserting these columns between 2 columns. Any idea how to have it do the following things:

1. automatically copy the format from the column/cell to the left and enter the value as 0

2. automatically update the formulas in the columns to the right to include the new columns

It can be added to the code you provided earlier if that is ok. That way it will all happen with one click.

I really appreciate your time!!

 

[lenze]

Can you explain exactly what you mean with Cell references? I'm not clear whrere you source Column is located!! Wher are the formulas located?/ What are the formulas?

lenze

 

[JoselynW]

Here is a screenshot. The columns will be added after "aa-2" and before "Combined". "Combined" and "Average/Benchmark" are formulas and are not updating when the new columns are added. Is there a way I can send you the file so you can look at the entire thing? Your like an excel angel! Thank you for your time!
<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=833><COLGROUP><COL style="WIDTH: 329pt; mso-width-source: userset; mso-width-alt: 16054" width=439><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #ddd9c4; WIDTH: 329pt; HEIGHT: 16.9pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=22 width=439> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #ddd9c4; WIDTH: 75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 width=100> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ddd9c4; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=87> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #b2b2b2; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=107> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b2b2b2; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=100>Average/</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 16.9pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=22 vAlign=top align=left><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: 19; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 330pt; HEIGHT: 16.5pt; VISIBILITY: visible; MARGIN-LEFT: 0px; mso-wrap-style: square; v-text-anchor: top" id=Rectangle_x0020_2 o:insetmode="auto" o:button="t" o:gfxdata="UEsDBBQABgAIAAAAIQC75UiUBQEAAB4CAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbKSRvU7DMBSF
dyTewfKKEqcMCKEmHfgZgaE8wMW+SSwc27JvS/v23KTJgkoXFsu+P+c7Ol5vDoMTe0zZBl/LVVlJ
gV4HY31Xy4/tS3EvRSbwBlzwWMsjZrlprq/W22PELHjb51r2RPFBqax7HCCXIaLnThvSAMTP1KkI
+gs6VLdVdad08ISeCho1ZLN+whZ2jsTzgcsnJwldluLxNDiyagkxOquB2Knae/OLUsyEkjenmdzb
mG/YhlRnCWPnb8C898bRJGtQvEOiVxjYhtLOxs8AySiT4JuDystlVV4WPeM6tK3VaILeDZxIOSsu
ti/jidNGNZ3/J08yC1dNv9v8AAAA//8DAFBLAwQUAAYACAAAACEArTA/8cEAAAAyAQAACwAAAF9y
ZWxzLy5yZWxzhI/NCsIwEITvgu8Q9m7TehCRpr2I4FX0AdZk2wbbJGTj39ubi6AgeJtl2G9m6vYx
jeJGka13CqqiBEFOe2Ndr+B03C3WIDihMzh6RwqexNA281l9oBFTfuLBBhaZ4ljBkFLYSMl6oAm5
8IFcdjofJ0z5jL0MqC/Yk1yW5UrGTwY0X0yxNwri3lQgjs+Qk/+zfddZTVuvrxO59CNCmoj3vCwj
MfaUFOjRhrPHaN4Wv0VV5OYgm1p+LW1eAAAA//8DAFBLAwQUAAYACAAAACEAS98NnwcEAACnDgAA
HwAAAGNsaXBib2FyZC9kcmF3aW5ncy9kcmF3aW5nMS54bWzUV01v2zgQvS+w/4HgdZFYkuVPVCmy
3qZbIEiDuEXPNEXZQihSS9KO3V+/MyRlK25Q7MfJF5vkDIfzho/k07v3+0aSnTC21qqg6XVCiVBc
l7VaF/Trl7urKSXWMVUyqZUo6EFY+v7m11/esfnasHZTcwIRlJ2zgm6ca+eDgeUb0TB7rVuhwFZp
0zAHXbMelIa9QORGDrIkGQ8aVit6cwr1B3OMbE39H0JJzZ9FuWBqxyyElHzeH4k5Sv7/I7O52n00
7bJ9NJg5f9g9GlKXBYXKKdZAieggGqIbdAdns9anAPvKNOivq4rsC5rleUbJoaB5OkuTJMQSe0c4
GPN0Oh6OYSEODlkyG42iA998/sl0vvnw8wCQXkgDGr3UbIuJqd2PWIcd1ifBgRxrKUh2hI3uHeZu
qo3l+pdoh7PJLKJNZ8lkhGsck2Xz1lj3UeiGYKOgBpLxdGK7e+uCa+eCSKyWdXlXS+k7Zr1aSEN2
TMLeJVmCdQ1Ubv4RTRpmnrftFddNy1y9qmXtDp7WlDR8/mmttGEriXzoTkia/0C/puZGW125a4gz
AA7UXHSnBIKlSTgjAcur/KXym6MRTzDjCBSnK7XbL/3+uf3vujyg8wr+gaxGQ60ArG35XQ2Fu2fW
PTIDBxgG4Spwn+GnkvqloFzWLSUbbb6fj6EfoAELJS9wERTU/rVlRlAiPylb0Fma5xDO+U4+mmTQ
MX3Lqm9R22ahYR9Sn5Vvor+TXbMyuvmmTXmLq4KJKQ5rQ4LOdJ2Fgz6Y4L7h4vbWt8Pm3KtlC1ua
em4gIb7svzHTRtY4OF0PerlhrXiLPME31Pp263RVR2aFaqJBWrd0Byl8eydT4DoBdtz7FGpVCuWT
RnMpqiew2u+QDh5vHHu1rf7yFEdiSpdGzr/yksA4RdyhFRXjQLHfGnUlXTiBgp0ZBAsGbs8M3MbY
ISvPHhnyj1CyExS/h1Dey8ODICKe4QmPJ+hF4kEQEU9+wpMOJ+kYGHWBG4QoIqBRD9A0m04vExCi
iIDGJ0BZNoUNusgdQhQR0KQHaJIPQcddIuUQRQQ0PQFCNPllAkIUEdCsB2g8mlzopYAowovUe129
oEF4TK7hcwVff9A+bO6hGxiXIEYLKtTV1yVICXxl/X6u8PX3nu5mIWv+TP4URhCnyQflhCFuI8jD
tllBU1cEtDTIMItiyvkUQiKwOI4c5dTWimWLAjjIr05v2aMUUE+igk8DVPUUszx72xnnoAzGwbRh
pQhadAR3hJf1qOXwUwrVQKgEBsRAFWi+Y+wga46eIUgXO6QW/XGqqCrI+Dg5KpBumbcmH2f4lbU6
TW5q0LhvITuplir4+/RjYbxEhYGzTzTvEj8p8Tuw37/5GwAA//8DAFBLAwQUAAYACAAAACEAkDqi
WTYHAAACJAAAGgAAAGNsaXBib2FyZC90aGVtZS90aGVtZTEueG1s7FpPb9s2FL8P2HcgdG/9P42D
OkXs2M3Wpg1it0OPtExLbChRoOikvg3tccCAYd2wy4Dddhi2FWiBXbpPk63D1gH9CnskJVuM6SVp
gy0pkoMjPT2+v3yPP5G6fuNRxNA+ESnlccurXC17iMQ+H9E4aHn3Br0rqx5KJY5HmPGYtLwpSb0b
6x9+cB2v+YwmQ47FaBCSiCAQFKdruOWFUiZrpVLqAxmnV3lCYng25iLCEm5FUBoJfAAKIlaqlssr
pQjT2FsHiVIJ6jL4iWWqCD4TfSWGoBhHoP3ueEx9onlHexXFkU7TDhNoH7OWBzJH/GBAHkkPMZxK
eNDyyvrPK61fL+G1bBCTS8YWxvX0XzYuGzDaq2qdIhjOlFZ69ea1TS9zP/JP4n+Exd4kueLzKMGS
DimjcqpD4aHIX/soiLnAQwb+5mJxpb4gN6K+4Ckfy6sgp8R1ZPLIgrBK2cQ1d1xbzuSiA91ut9Ot
5JrOqwOZ5dj3YW6Y7BWzUO+tVtrnPgsF683lYjY65Ua5fkEc0dYbR2oLddFst9uN5nmfVgXrzWV9
wZHV8kp9o3oxHNHWG0caC47U2xudzsrFcERbbxxZWXCkd625Ur8gjmjrQ0bjvQU31MLU6533fMxs
H3O25fRjFfxYBdxgAMB5XUDm5gMOmOEKlZQxj+UylBHhh1z0gEExMlisYySnCRljH1bnDo6GgmKF
EfAawYUnhuSnCySlC6W+oIlseR8nOPYKLG9e/vjm5XP05uWzw8cvDh//cvjkyeHjn40sa+AWjoPi
wNfff/H3t5+iv55/9/rpV27+tMj/+0+f/fbrl25GwE5zD199/eyPF89effP5nz88dbBvAEwpsg9o
RFJ0hxygXR6BbzowtuVkKE43YhBiao3AIch2iO7K0GK8M8XMxdcmdvDuC4CNLsabk4eWrf1QTCR1
aL4VRhbjNuesDRDOFYBbSlchwoNJHLiVi0mRbxfjfZfuDo6t1HYnCeDlfFJase+ExDJzh+FY4oDE
RCL1jO8R4vDuAaVWXLdz3IkeUNTG1BmSAR1aE2k+aItGkJepy2dItRWb7fuozZnL602yb3NCQWDm
MH5AmBXGm3giceQSOcARKwb8Npahy8j+VPhFvm4qIdMBYRx1RyRNXWPuCvC3kPRbGBqpM+3bbBrZ
nELSPZfM25jzIucm3+uEGF4oHFHo0zgs8n6U7sEUxWiHSxf7NrcrRN1DHnC8NN33KbHSfXwjuEcD
y6T5BFFPJsLhxU3Crfnbn7IxJrrIoKVbnTqi8ArVW9q2GYW+bTRctu2WtwGLmKt4to4062V8F7BF
b+JJvEOgKhaXqMsOfdmhvfe+Qy+r5bPvy/NWDF1aARKDtTXyjpYC7zFlrC+njNxONfZOYQEa9YCo
xumtRTLbgktCuFSVDAosvkBgPQYJLj+hMuyHOAHcXvGUkCDNRAcpSngKO4Wa7JSt+AH7S7PP2FBv
bqZzpFhu85Eh1xRZ05UdetsS7NJWBXo3M1dUUwJOqqx2LRMKMt9GWUUZdWJtFW2aboqWtpnLTteA
OIsmIBsEeAiivAKbu0o1vO9gRkYq7iZHeVp0Fs4yRWmIRyTLkfJ7MUcVnaR8rvxLjtRL7TFRK2hr
KrHvoO0kSSqqqy9Rl2fvXbKUz+B5lkDa0XJkcbE4WYwOWl6zUW14yMdJyxvDqzJcRglkPVVgErMA
jhd8Kcy0P7aYdZXPs9nMHbOLoAKbpSbuCw5bfSARqdzEaWimhn6UTQEWK03G/moDwnpWDji60cms
qK3CZPjfrIA42qkl4zHxZTHZBYqKnbnNWimfSCL64egADdlE7GJIv5qq4M+IprDjoTuCuoGDGRVt
/chuzlnRFTf3NR/IOK8bTCoMmCUhztYB1TvyFmP80IvALDj6rhA3CLozqDrqp4+x7kXve4yLhX8Z
47M5Kjw6j08bY4V6YDepNlKCfDiwFRipptfyuJAhhwUlCanfE4AB9TIAhQ/ntPAYahtOl/V/QfbV
f9M+jQwljcGmgNylARIUoIUMBSE7sMLoRnKMsEoGQ4zIXJABi3Nz08SYPST7hA3UcraiYJqHQuha
emHIOrrmO1qx9n3WDIeBwqvF1mktSjOEaLrGfw1iTV8Gp+wlVWPTPP4zEx0g1ozXw3MYVXREPZgj
5no+lUBZYVVvZo3yLU04JWoyi8+Cx9VGbhxkcdFjIM6wLZzWh0j9AJShwmfmSwSFjQZ8F5ZJBB8h
KGEwbWBWXzEYEqm1zhCHgIEN0UwmJcqENkPBKmo57jrjl5aZ3iPBVpadJN+nDPYMZ9vqrFo8y2Bn
EbZibWhLQw2ZPVqiQBrn76Q6Ma4vUrZxgoZBpeXBVyGQ6EdwBd+VeECrKlpV0eAKPhYB3Gu+V2h5
2UVOgeeGMuOp5ZRazlPPKfWc0sgpgLOzw9GcAseh+qQOPhNRh3Qeys+7AIxnB3d5U7U+21n/BwAA
//8DAFBLAwQUAAYACAAAACEAnGZGQbsAAAAkAQAAKgAAAGNsaXBib2FyZC9kcmF3aW5ncy9fcmVs
cy9kcmF3aW5nMS54bWwucmVsc4SPzQrCMBCE74LvEPZu0noQkSa9iNCr1AcIyTYtNj8kUezbG+hF
QfCyMLPsN7NN+7IzeWJMk3ccaloBQae8npzhcOsvuyOQlKXTcvYOOSyYoBXbTXPFWeZylMYpJFIo
LnEYcw4nxpIa0cpEfUBXNoOPVuYio2FBqrs0yPZVdWDxkwHii0k6zSF2ugbSL6Ek/2f7YZgUnr16
WHT5RwTLpRcWoIwGMwdKV2edNS1dgYmGff0m3gAAAP//AwBQSwECLQAUAAYACAAAACEAu+VIlAUB
AAAeAgAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQABgAIAAAA
IQCtMD/xwQAAADIBAAALAAAAAAAAAAAAAAAAADYBAABfcmVscy8ucmVsc1BLAQItABQABgAIAAAA
IQBL3w2fBwQAAKcOAAAfAAAAAAAAAAAAAAAAACACAABjbGlwYm9hcmQvZHJhd2luZ3MvZHJhd2lu
ZzEueG1sUEsBAi0AFAAGAAgAAAAhAJA6olk2BwAAAiQAABoAAAAAAAAAAAAAAAAAZAYAAGNsaXBi
b2FyZC90aGVtZS90aGVtZTEueG1sUEsBAi0AFAAGAAgAAAAhAJxmRkG7AAAAJAEAACoAAAAAAAAA
AAAAAAAA0g0AAGNsaXBib2FyZC9kcmF3aW5ncy9fcmVscy9kcmF3aW5nMS54bWwucmVsc1BLBQYA
AAAABQAFAGcBAADVDgAAAAA=
" type="#_x0000_t75" o:spid="_x0000_s11307"><v:fill o:detectmouseclick="t"></v:fill><v:imagedata o:title="" src="file:///C:\Users\Joselyn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata><o:lock aspectratio="f" v:ext="edit"></o:lock><?xml:namespace prefix = x ns = "urn:schemas-microsoft-comffice:excel" /><x:ClientData ObjectType="Pict"><x:SizeWithCells></x:SizeWithCells><x:CF>Bitmap</x:CF> <x:AutoPict></x:AutoPict></x:ClientData></v:shape><v:shape style="Z-INDEX: 20; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 0.75pt; HEIGHT: 16.5pt; VISIBILITY: visible; MARGIN-LEFT: 0px; mso-wrap-style: square; v-text-anchor: top" id=Rectangle_x0020_3 o:insetmode="auto" o:button="t" o:gfxdata="UEsDBBQABgAIAAAAIQC75UiUBQEAAB4CAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbKSRvU7DMBSF
dyTewfKKEqcMCKEmHfgZgaE8wMW+SSwc27JvS/v23KTJgkoXFsu+P+c7Ol5vDoMTe0zZBl/LVVlJ
gV4HY31Xy4/tS3EvRSbwBlzwWMsjZrlprq/W22PELHjb51r2RPFBqax7HCCXIaLnThvSAMTP1KkI
+gs6VLdVdad08ISeCho1ZLN+whZ2jsTzgcsnJwldluLxNDiyagkxOquB2Knae/OLUsyEkjenmdzb
mG/YhlRnCWPnb8C898bRJGtQvEOiVxjYhtLOxs8AySiT4JuDystlVV4WPeM6tK3VaILeDZxIOSsu
ti/jidNGNZ3/J08yC1dNv9v8AAAA//8DAFBLAwQUAAYACAAAACEArTA/8cEAAAAyAQAACwAAAF9y
ZWxzLy5yZWxzhI/NCsIwEITvgu8Q9m7TehCRpr2I4FX0AdZk2wbbJGTj39ubi6AgeJtl2G9m6vYx
jeJGka13CqqiBEFOe2Ndr+B03C3WIDihMzh6RwqexNA281l9oBFTfuLBBhaZ4ljBkFLYSMl6oAm5
8IFcdjofJ0z5jL0MqC/Yk1yW5UrGTwY0X0yxNwri3lQgjs+Qk/+zfddZTVuvrxO59CNCmoj3vCwj
MfaUFOjRhrPHaN4Wv0VV5OYgm1p+LW1eAAAA//8DAFBLAwQUAAYACAAAACEActymzwgEAAChDgAA
HwAAAGNsaXBib2FyZC9kcmF3aW5ncy9kcmF3aW5nMS54bWzUV01v2zgQvS+w/4HgdZFYkmVbNqoU
WW/TLRCkQdyiZ5qibCEUqSVpx+6v3xmSsh03KPbj5ItNcoajeZxH8vHd+10ryVYY22hV0vQ6oUQo
rqtGrUr69cvdVUGJdUxVTGolSroXlr6/+fWXd2y2MqxbN5xABGVnrKRr57rZYGD5WrTMXutOKLDV
2rTMQdesBpVhLxC5lYMsScaDljWK3hxD/cEcIxvT/IdQUvNnUc2Z2jILISWfnY7EHCX//5HZTG0/
mm7RPRrMnD9sHw1pqpLCyinWwhLRQTREN+gOzmatjgF2tWnRX9c12ZU0y/OMkn1J83SaJkmIJXaO
cDDmRTGihIM1S6ajUbTy9eefzOXrDz+ZDYmFBKBxkpTtMCW1/RFl3qN8EhxosZKCDA+A0b1H20+1
caH+Fc60GE4n0wg1nSaTEX7jkCybdca6j0K3BBslNZCMJxLb3lsXXHsXRGK1bKq7RkrfMavlXBqy
ZRKqlmTJGEoXSNz+I4K0zDxvuiuu2465ZtnIxu09oSlp+ezTSmnDlhKZ0O+NNP+BeG3Djba6dtcQ
ZwDVb7jo9wcES5OwOwKWV/lL5YujEU8w4wgsTr/Ubrfw9XO733W1R+cl/ANNjYa1ArC243cNLNw9
s+6RGdi6MAiHgPsMP7XULyXlsukoWWvz/XwM/QANWCh5gSOgpPavDTOCEvlJ2ZJO0zyHcM538tEk
g445tSxPLWrTzjXUIfVZ+Sb6O9k3a6Pbb9pUt/hVMDHF4duQoDN9Z+6gDyY4abi4vfXtUJx7teig
pKnnBhLiy+4bM11kjYN99aAXa9aJt8gTfMNa326crpvIrLCaaJDWLdxeCt/eyhS4ToAd9z6FRlVC
+aTRXIn6Caz2O6SDGxvHXpXVH5viQEzp0sj5V14SGKeI23eiZhwo9lurrqQLO1CwM4NgwcDtmYHb
GDtk5dkjQ/4RSnaE4msIy3t5eBBExDM84vEEvUg8CCLiyY940uEkHQOjLrBAiCICGp0AKrKiuExA
iCICGh8BZVkBBbrICiGKCGhyAmiSD0HBXSLlEEUEVBwBIZr8MgEhighoegJoPJpc6KGAKMKNdHK7
ekGD8JhcwUMFb3/QPmzmoRsYlyBGSyrU1dcFSAm8ZX09l3j7e093M5cNfyZ/CiOI0+SDcsIQtxbk
YdMuoalrAkIaZJhFMeV8CiER+DiOHOTUxopFhwI4yK9eb9mDFFBPooZHAep5ilme3e2Mc1AG42Ba
s0oELTqCM8JretRy+IhCNRBWAgNioBo03yF2kDUHzxCkjx1Si/44VdQ1ZHyYHBVI/5m3Jh9m+C9r
dZzcNqBx30J2VC118Pfpx4XxEhUGzh5n3iU+JvEFeNq/+RsAAP//AwBQSwMEFAAGAAgAAAAhAJA6
olk2BwAAAiQAABoAAABjbGlwYm9hcmQvdGhlbWUvdGhlbWUxLnhtbOxaT2/bNhS/D9h3IHRv/T+N
gzpF7NjN1qYNYrdDj7RMS2woUaDopL4N7XHAgGHdsMuA3XYYthVogV26T5Otw9YB/Qp7JCVbjOkl
aYMtKZKDIz09vr98jz+Run7jUcTQPhEp5XHLq1wte4jEPh/ROGh59wa9K6seSiWOR5jxmLS8KUm9
G+sffnAdr/mMJkOOxWgQkoggEBSna7jlhVIma6VS6gMZp1d5QmJ4NuYiwhJuRVAaCXwACiJWqpbL
K6UI09hbB4lSCeoy+Illqgg+E30lhqAYR6D97nhMfaJ5R3sVxZFO0w4TaB+zlgcyR/xgQB5JDzGc
SnjQ8sr6zyutXy/htWwQk0vGFsb19F82Lhsw2qtqnSIYzpRWevXmtU0vcz/yT+J/hMXeJLni8yjB
kg4po3KqQ+GhyF/7KIi5wEMG/uZicaW+IDeivuApH8urIKfEdWTyyIKwStnENXdcW87kogPdbrfT
reSazqsDmeXY92FumOwVs1DvrVba5z4LBevN5WI2OuVGuX5BHNHWG0dqC3XRbLfbjeZ5n1YF681l
fcGR1fJKfaN6MRzR1htHGguO1Nsbnc7KxXBEW28cWVlwpHetuVK/II5o60NG470FN9TC1Oud93zM
bB9ztuX0YxX8WAXcYADAeV1A5uYDDpjhCpWUMY/lMpQR4Ydc9IBBMTJYrGMkpwkZYx9W5w6OhoJi
hRHwGsGFJ4bkpwskpQulvqCJbHkfJzj2CixvXv745uVz9Obls8PHLw4f/3L45Mnh45+NLGvgFo6D
4sDX33/x97efor+ef/f66Vdu/rTI//tPn/3265duRsBOcw9fff3sjxfPXn3z+Z8/PHWwbwBMKbIP
aERSdIccoF0egW86MLblZChON2IQYmqNwCHIdojuytBivDPFzMXXJnbw7guAjS7Gm5OHlq39UEwk
dWi+FUYW4zbnrA0QzhWAW0pXIcKDSRy4lYtJkW8X432X7g6OrdR2Jwng5XxSWrHvhMQyc4fhWOKA
xEQi9YzvEeLw7gGlVly3c9yJHlDUxtQZkgEdWhNpPmiLRpCXqctnSLUVm+37qM2Zy+tNsm9zQkFg
5jB+QJgVxpt4InHkEjnAESsG/DaWocvI/lT4Rb5uKiHTAWEcdUckTV1j7grwt5D0WxgaqTPt22wa
2ZxC0j2XzNuY8yLnJt/rhBheKBxR6NM4LPJ+lO7BFMVoh0sX+za3K0TdQx5wvDTd9ymx0n18I7hH
A8uk+QRRTybC4cVNwq3525+yMSa6yKClW506ovAK1VvathmFvm00XLbtlrcBi5ireLaONOtlfBew
RW/iSbxDoCoWl6jLDn3Zob33vkMvq+Wz78vzVgxdWgESg7U18o6WAu8xZawvp4zcTjX2TmEBGvWA
qMbprUUy24JLQrhUlQwKLL5AYD0GCS4/oTLshzgB3F7xlJAgzUQHKUp4CjuFmuyUrfgB+0uzz9hQ
b26mc6RYbvORIdcUWdOVHXrbEuzSVgV6NzNXVFMCTqqsdi0TCjLfRllFGXVibRVtmm6KlraZy07X
gDiLJiAbBHgIorwCm7tKNbzvYEZGKu4mR3ladBbOMkVpiEcky5HyezFHFZ2kfK78S47US+0xUSto
ayqx76DtJEkqqqsvUZdn712ylM/geZZA2tFyZHGxOFmMDlpes1FteMjHScsbw6syXEYJZD1VYBKz
AI4XfCnMtD+2mHWVz7PZzB2zi6ACm6Um7gsOW30gEancxGlopoZ+lE0BFitNxv5qA8J6Vg44utHJ
rKitwmT436yAONqpJeMx8WUx2QWKip25zVopn0gi+uHoAA3ZROxiSL+aquDPiKaw46E7grqBgxkV
bf3Ibs5Z0RU39zUfyDivG0wqDJglIc7WAdU78hZj/NCLwCw4+q4QNwi6M6g66qePse5F73uMi4V/
GeOzOSo8Oo9PG2OFemA3qTZSgnw4sBUYqabX8riQIYcFJQmp3xOAAfUyAIUP57TwGGobTpf1f0H2
1X/TPo0MJY3BpoDcpQESFKCFDAUhO7DC6EZyjLBKBkOMyFyQAYtzc9PEmD0k+4QN1HK2omCah0Lo
WnphyDq65jtasfZ91gyHgcKrxdZpLUozhGi6xn8NYk1fBqfsJVVj0zz+MxMdINaM18NzGFV0RD2Y
I+Z6PpVAWWFVb2aN8i1NOCVqMovPgsfVRm4cZHHRYyDOsC2c1odI/QCUocJn5ksEhY0GfBeWSQQf
IShhMG1gVl8xGBKptc4Qh4CBDdFMJiXKhDZDwSpqOe4645eWmd4jwVaWnSTfpwz2DGfb6qxaPMtg
ZxG2Ym1oS0MNmT1aokAa5++kOjGuL1K2cYKGQaXlwVchkOhHcAXflXhAqypaVdHgCj4WAdxrvldo
edlFToHnhjLjqeWUWs5Tzyn1nNLIKYCzs8PRnALHofqkDj4TUYd0HsrPuwCMZwd3eVO1PttZ/wcA
AP//AwBQSwMEFAAGAAgAAAAhAJxmRkG7AAAAJAEAACoAAABjbGlwYm9hcmQvZHJhd2luZ3MvX3Jl
bHMvZHJhd2luZzEueG1sLnJlbHOEj80KwjAQhO+C7xD2btJ6EJEmvYjQq9QHCMk2LTY/JFHs2xvo
RUHwsjCz7DezTfuyM3liTJN3HGpaAUGnvJ6c4XDrL7sjkJSl03L2DjksmKAV201zxVnmcpTGKSRS
KC5xGHMOJ8aSGtHKRH1AVzaDj1bmIqNhQaq7NMj2VXVg8ZMB4otJOs0hdroG0i+hJP9n+2GYFJ69
elh0+UcEy6UXFqCMBjMHSldnnTUtXYGJhn39Jt4AAAD//wMAUEsBAi0AFAAGAAgAAAAhALvlSJQF
AQAAHgIAABMAAAAAAAAAAAAAAAAAAAAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAYACAAA
ACEArTA/8cEAAAAyAQAACwAAAAAAAAAAAAAAAAA2AQAAX3JlbHMvLnJlbHNQSwECLQAUAAYACAAA
ACEActymzwgEAAChDgAAHwAAAAAAAAAAAAAAAAAgAgAAY2xpcGJvYXJkL2RyYXdpbmdzL2RyYXdp
bmcxLnhtbFBLAQItABQABgAIAAAAIQCQOqJZNgcAAAIkAAAaAAAAAAAAAAAAAAAAAGUGAABjbGlw
Ym9hcmQvdGhlbWUvdGhlbWUxLnhtbFBLAQItABQABgAIAAAAIQCcZkZBuwAAACQBAAAqAAAAAAAA
AAAAAAAAANMNAABjbGlwYm9hcmQvZHJhd2luZ3MvX3JlbHMvZHJhd2luZzEueG1sLnJlbHNQSwUG
AAAAAAUABQBnAQAA1g4AAAAA
" type="#_x0000_t75" o:spid="_x0000_s11308"><v:fill o:detectmouseclick="t"></v:fill><v:imagedata o:title="" src="file:///C:\Users\Joselyn\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png"></v:imagedata><o:lock aspectratio="f" v:ext="edit"></o:lock><x:ClientData ObjectType="Pict"><x:SizeWithCells></x:SizeWithCells><x:CF>Bitmap</x:CF> <x:AutoPict></x:AutoPict></x:ClientData></v:shape><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #b2b2b2; WIDTH: 329pt; HEIGHT: 16.9pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl71 height=22 width=439>Office Name</TD></TR></TBODY></TABLE></TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: #b2b2b2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>aa-1</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b2b2b2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81>aa-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #b2b2b2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>Combined</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b2b2b2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>Benchmark</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #c0504d; HEIGHT: 16.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: #333333 0.5pt solid" class=xl74 height=22>Total income (as per income statement)(top line)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$1,584,532.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$1,584,532.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #333333 0.5pt solid" class=xl82>$3,169,064.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #333333 0.5pt solid" class=xl82>$1,056,354.67</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #c0504d; HEIGHT: 16.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: #333333 0.5pt solid" class=xl74 height=22>Other Income </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$0.00</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #c0504d; HEIGHT: 16.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: #333333 0.5pt solid" class=xl74 height=22>Cost of sales </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$1,297,736.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$1,297,736.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$2,595,472.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$865,157.33</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #c0504d; HEIGHT: 16.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: #333333 0.5pt solid" class=xl74 height=22>Income (flowthroughs)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$15,845.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$15,845.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$31,690.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$10,563.33</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #c0504d; HEIGHT: 16.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: #333333 0.5pt solid" class=xl74 height=22>Personal sales commissions included in the Total income</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 align=right>$0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl73>$0.00</TD></TR><TR style="HEIGHT: 16.9pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: #92d050; HEIGHT: 16.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: #333333 0.5pt solid" class=xl76 height=22>True Company Dollar (Hidden cell)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 align=right>$270,951.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 align=right>$270,951.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 align=right>$541,902.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 align=right>$180,634.00</TD></TR></TBODY></TABLE>

 

[lenze]

So the columns are added after Column "C"??
What Format do you want carried over??

lenze

See my PM

 

[JoselynW]

The sum.

 

[JoselynW]

I'm sorry. I meant if it is a $, then keep that format. If it is a %, keep %. If it is a standard number then keep that. Possibly automatically populate with a 0, so that it is not blank.

 

[lenze]

Looked at your sheet. (I must be in a good mood! I almost NEVER work on sheets with more than 4 colors!!!).
Anyway, is this correct?
When you enter the number of offices, you want to insert that number of Columns betwen "C" and "D".
You also want these new Columns to be formatted like Column "D"??
And you want the Formulas from "D" also inserted in the columns??

lenze

BTW: You probably should upload this to box.net or some other share site so others can see your shett. SOmeone else may have a better idea that I!!

 

[JoselynW]

Thank you for looking at my sheet. Yes, i want to insert the new columns in between columns "c" and "D". I want the new columns to adapt the format from column "C" and I want the formulas that exist in column "D" to update to include column "b", "c" and all the new columns.

Does that help? Thanks for your help.

Joselyn