Transpositional Issue

[jlkirk]

Please see attached. This is one example of several (>25) worksheets where I am trying to come up with a formula that will "transpose" or link the data in these worksheets into a summary Sheet 1, an example of which is also attached. In addition, the data in worksheets B, D & E (and others) should be transposed or linked to Sheet 1 as negative numbers. Any ideas? I have been trying sumproduct, but can't seem to get the syntax right. Any ideas?
Thanks in advance.

Excel Workbook
	A	B	C	D	E	F	G
1	A
2
3	Date	Jan	Feb	Mar	Apr	May	Jun
4	1	1,790	1,385	1,870	1,840	1,489	1,835
5	2	1,694	1,572	2,191	1,751	1,489	1,393
6	3	1,630	1,929	2,005	1,632	1,491	1,348
7	4	1,023	1,902	1,967	1,705	1,416	187
8	5	2,291	1,897	1,707	1,763	1,450	1,617
9	6	2,304	1,815	1,493	1,667	1,477	1,522
10	7	2,152	1,253	1,747	1,666	1,525	1,509
11	8	2,053	1,272	1,799	1,615	1,595	1,506
12	9	1,801	1,341	1,742	1,526	1,196	1,538
13	10	1,479	1,242	1,785	1,582	1,200	1,492
14	11	1,792	2,599	2,144	385	1,049	1,439
15	12	1,963	2,242	1,858	-	2,076	1,433
16	13	1,989	1,770	1,896	1,202	1,714	1,358
17	14	1,912	1,301	1,317	465	1,626	1,289
18	15	1,767	1,330	1,920	327	1,620	1,425
19	16	1,690	1,430	1,950	2,040	1,615	1,303
20	17	1,638	2,336	1,714	1,730	1,588	1,265
21	18	1,459	2,121	1,861	1,626	1,501	1,132
22	19	1,462	2,332	1,814	1,355	1,270	1,163
23	20	2,425	2,176	1,765	1,535	1,325	1,186
24	21	2,103	2,031	1,662	1,329	1,413	1,221
25	22	1,525	1,916	1,760	1,391	1,296	1,225
26	23	2,219	1,845	1,738	1,536	1,193	1,187
27	24	1,632	1,721	1,745	1,573	1,047	1,203
28	25	1,453	1,751	1,335	1,546	1,473	1,179
29	26	2,265	1,488	1,765	1,308	1,556	1,223
30	27	2,174	1,146	1,702	1,678	1,498	1,218
31	28	1,606	1,868	1,678	1,571	1,468	1,093
32	29	1,501	----	1,705	1,599	1,485	1,005
33	30	1,962	----	1,807	1,606	1,560	1,044
34	31	2,143	----	1,960	----	1,481	----
A

Excel Workbook
	A	B	C	D	E	F
1		A	B	C	D	E
2	1/1/2007
3	1/2/2007
4	1/3/2007
5	1/4/2007
6	1/5/2007
7	1/6/2007
8	1/7/2007
9	1/8/2007
10	1/9/2007
11	1/10/2007
12	1/11/2007
13	1/12/2007
14	1/13/2007
15	1/14/2007
16	1/15/2007
17	1/16/2007
18	1/17/2007
19	1/18/2007
20	1/19/2007
21	1/20/2007
22	1/21/2007
23	1/22/2007
24	1/23/2007
25	1/24/2007
26	1/25/2007
27	1/26/2007
28	1/27/2007
29	1/28/2007
30	1/29/2007
31	1/30/2007
32	1/31/2007
33	2/1/2007
34	2/2/2007
35	2/3/2007
36	2/4/2007
37	2/5/2007
38	2/6/2007
39	2/7/2007
40	2/8/2007
41	2/9/2007
42	2/10/2007
43	2/11/2007
44	2/12/2007
45	2/13/2007
46	2/14/2007
47	2/15/2007
48	2/16/2007
49	2/17/2007
50	2/18/2007
51	2/19/2007
52	2/20/2007
53	2/21/2007
54	2/22/2007
55	2/23/2007
56	2/24/2007
57	2/25/2007
58	2/26/2007
59	2/27/2007
60	2/28/2007
Sheet1

Thanks in advance.

 

[jlkirk]

EXCELLENT!!!!! Thanks for all of your help and patience!

 

[jlkirk]

Hi Andrew,
Thanks again for your help. Unfortunately, I have run into another issue with respect to my original problem: instead of "transposing" the data to Sheet1 in the same worbook, I need to "transpose" the data to Sheet1 of another, separate workbook, that let's call Workbook2. How would I change the formula? I have tried several ways, but nothing seems to work. The cell locations on Sheet1 of Workbook2 would be the same as originally posed.
Thanks again.

 

[Andrew Poulsom]

Try:

=INDEX(INDIRECT("'[Book1.xls]"&B$1&"'!$A$3:$M$34"),MATCH(DAY($A2),INDIRECT("'[Book1.xls]"&B$1&"'!$A$3:$A$34"),FALSE),MATCH(TEXT($A2,"mmm"),INDIRECT("'[Book1.xls]"&B$1&"'!$A$3:$M$3"),FALSE))*IF(ISNUMBER(SEARCH(B$1,"BDE")),-1,1)

where Book1.xls is the name of the source workbook, which must be open.

 

[jlkirk]

Andrew,
One quick question: Your SEARCH argument looks for "BDE". I assume they are the worksheets in workbook1. Should the argument instead be OR={"B,"D","E"}?
Thanks

 

[Andrew Poulsom]

The first argument for SEARCH is find_text and the second is within_text. So B, D and E (and BD, DE and BDE) exist within BDE. If your sheets were named Sheet1, Sheet2 etc, you could use:

ISNUMBER(SEARCH(B$1,"Sheet2Sheet4Sheet5"))

 

[RoryA]

Incidentally, this is cross posted at the Lounge (not the MrExcel lounge!) here.

 

[jlkirk]

Thanks for pointing this out Rory. Yes I do cross-post certain topics to get a feel for the different views. As you can see from the answers, the answers are quite different. I didn't realize there was a policy on either this or Woody's Board that frowns on this practice. I have come to appreciate and look to both Boards for guidance in all Office matters. I apologize for any offense.