Splitting formulas in cell

  • Thread starter Thread starter Ben
  • Start date Start date
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
 
Can you work the formula backwards, by creating the formula for D2 an
E2 separately, then combining them in C2
 
Do the cells contain the same number of characters? If so you could use the Left and Right functions.
 
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
 
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
 
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]...
 
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.
 
Back
Top