Insert a column into an array formula?

charlie79 · May 16th, 2002, 08:40 AM

I haved a formula, an array formula, that goes from let's say A1:Z1. Is it impossible to insert a new column, say in between columns c & d?

Asala42 · May 16th, 2002, 08:46 AM

Hey,

I believe you'll have to clear the array, insert the column, and then toss the formula in again.

Maybe someone has a VBA soln?

Adam

charlie79 · May 16th, 2002, 09:00 AM

ok, so how do i clear the formula without losing it? know what i mean?

Asala42 · May 16th, 2002, 09:58 AM

Hey again,

Unfortunately I only see the "manual" method. That is to copy the formula from the formula bar, toss a ' in another cell and pate the formula in (effectively making a text version of the formula. After you inserted the extra column and highlighted the range, you can paste the formula (hitting Ctrl+shift+enter to get the brackets as usual).

Hope that helps - maybe someone will post a better answer.

Adam

charlie79 · May 16th, 2002, 11:33 AM

Thanks. I wasn't sure if there was an easier way to do it. I guess not. Thanks again!

Mark W. · May 16th, 2002, 11:41 AM

Quote:

On 2002-05-16 10:33, charlie79 wrote:
Thanks. I wasn't sure if there was an easier way to do it. I guess not. Thanks again!

There is! Select all of the cells that contain the array formula (A1:Z1, in your case). Activate the formula bar and press Control+Enter. Now, you can insert the new column. Once done select the cells that will contain the revised array formula, activate the formula bar, and re-enter using the Control+Shift+Enter key combination.

[ This Message was edited by: Mark W. on 2002-05-16 11:03 ]

charlie79 · May 16th, 2002, 11:54 AM

Great, thanks mark!

Asala42 · May 16th, 2002, 08:05 PM

good call Mark!

learned something new on this board again.

Thanks!
Adam

May 16th, 2002, 08:40 AM	#1
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	I haved a formula, an array formula, that goes from let's say A1:Z1. Is it impossible to insert a new column, say in between columns c & d? __________________ How long a minute is depends on which side of the bathroom door you are standing... Cheers

May 16th, 2002, 09:00 AM	#3
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	ok, so how do i clear the formula without losing it? know what i mean? __________________ How long a minute is depends on which side of the bathroom door you are standing... Cheers

May 16th, 2002, 11:33 AM	#5
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Thanks. I wasn't sure if there was an easier way to do it. I guess not. Thanks again! __________________ How long a minute is depends on which side of the bathroom door you are standing... Cheers

May 16th, 2002, 11:54 AM	#7
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Great, thanks mark! __________________ How long a minute is depends on which side of the bathroom door you are standing... Cheers

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 08:46 AM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hey, I believe you'll have to clear the array, insert the column, and then toss the formula in again. Maybe someone has a VBA soln? Adam

May 16th, 2002, 09:58 AM	#4
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hey again, Unfortunately I only see the "manual" method. That is to copy the formula from the formula bar, toss a ' in another cell and pate the formula in (effectively making a text version of the formula. After you inserted the extra column and highlighted the range, you can paste the formula (hitting Ctrl+shift+enter to get the brackets as usual). Hope that helps - maybe someone will post a better answer. Adam

May 16th, 2002, 08:05 PM	#8
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	good call Mark! learned something new on this board again. Thanks! Adam