Splitting formulas in cell

B

Ben

Hi,

I have a cell that combines two cells from another sheet.
Example: Sheet:MCD, Cell:C2 contains the following
[='Monthly Calander'!$M$3&'Monthly Calander'!A11]

On the first sheet 'Monthly Calander' the data is coming
from both horizontal and vertical where the combined
cells are simply vertical thus creating a lot of new work
to try and get the data split from the 1st sheet next to
the combined data on the 2nd sheet.

What formula can I put in Cell D2 to get the first half
of the formula in Cell C2 and then use a simliar formula
in E2 to get the 2nd part? This formula would then be
pasted in d3:e7000

Thanks for your Time
Ben
 
J

Janly

Can you work the formula backwards, by creating the formula for D2 an
E2 separately, then combining them in C2
 
B

BC

Do the cells contain the same number of characters? If so you could use the Left and Right functions.
 
G

Guest

Janly,

Good Idea. This time I wish I had done it that way. Do
you know an easy way to copy Data in the following
situation.
Sheet 1
A B C D E F
1 A1 B1 C1 D1 E1 F1
2 A2 B2 C2 D2 E2 F2
3 A3 B3 C3 D3 E3 F3
4 A4 B4 C4 D4 E4 F4

To the Following:
Sheet 2
A B
1 A1 B1
2 C1 D1
3 E1 F1
4 A2 B2
5 C2 D2
6 E2 F2
7 A3 B3
8 C3 D3
9 E3 F3
10 A4 B4
11 C4 D4
12 E4 F4

Ben
 
A

Anders S

Ben,

Here is one way to do it. I suppose your actual data range is different from the example, so you will have to adjust the formulas accordingly.

To make the formulas a bit shorter, define a name for the source range:
- select A1:F4 in Sheet1
- Insert>Name>Define, enter "myRange" (without the quotes) in the name box

Switch to Sheet2
- in A1:A12 enter =INDEX(myRange,INT((ROW()-1)/3)+1,MOD(ROW()*2-1,6))
- in B1:B12 enter =INDEX(myRange,INT((ROW()-1)/3)+1,MOD(ROW()*2-1,6)+1)

To get rid of the formulas in Sheet2 and freeze the values:
- select cols A and B
- Edit>Copy
- Edit>Paste Special>Values

HTH
Anders Silven

Janly,

Good Idea. This time I wish I had done it that way. Do
you know an easy way to copy Data in the following
situation.
Sheet 1
A B C D E F
1 A1 B1 C1 D1 E1 F1
2 A2 B2 C2 D2 E2 F2
3 A3 B3 C3 D3 E3 F3
4 A4 B4 C4 D4 E4 F4

To the Following:
Sheet 2
A B
1 A1 B1
2 C1 D1
3 E1 F1
4 A2 B2
5 C2 D2
6 E2 F2
7 A3 B3
8 C3 D3
9 E3 F3
10 A4 B4
11 C4 D4
12 E4 F4

Ben

-----Original Message-----

Can you work the formula backwards, by creating the formula for D2 and
E2 separately, then combining them in C2?


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
Click to expand...
 
G

Guest

Ander,

Thanks!
-----Original Message-----
Ben,

Here is one way to do it. I suppose your actual data
Click to expand...
range is different from the example, so you will have to
adjust the formulas accordingly.
To make the formulas a bit shorter, define a name for the source range:
- select A1:F4 in Sheet1
- Insert>Name>Define, enter "myRange" (without the quotes) in the name box

Switch to Sheet2
- in A1:A12 enter =INDEX(myRange,INT((ROW()-1)/3)+1,MOD (ROW()*2-1,6))
- in B1:B12 enter =INDEX(myRange,INT((ROW()-1)/3)+1,MOD (ROW()*2-1,6)+1)

To get rid of the formulas in Sheet2 and freeze the values:
- select cols A and B
- Edit>Copy
- Edit>Paste Special>Values

HTH
Anders Silven

<[email protected]> skrev i
Click to expand...
meddelandet news:[email protected]...
Janly,

Good Idea. This time I wish I had done it that way. Do
you know an easy way to copy Data in the following
situation.
Sheet 1
A B C D E F
1 A1 B1 C1 D1 E1 F1
2 A2 B2 C2 D2 E2 F2
3 A3 B3 C3 D3 E3 F3
4 A4 B4 C4 D4 E4 F4

To the Following:
Sheet 2
A B
1 A1 B1
2 C1 D1
3 E1 F1
4 A2 B2
5 C2 D2
6 E2 F2
7 A3 B3
8 C3 D3
9 E3 F3
10 A4 B4
11 C4 D4
12 E4 F4

Ben

-----Original Message-----

Can you work the formula backwards, by creating the formula for D2 and
E2 separately, then combining them in C2?


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
Click to expand...
.
Click to expand...
 
J

Janly

Now I'm lost. I'm not an excel guru, I'm simply an intermediate
everyday user, so, unfortunately, at the moment I don't know how to
copy info in that particular order. Hopefully, someone else with more
expertise can give you a solution.

I will try working on samples to see if I can get it. And will get
back to you as soon as I find something.
 
Top