Transpositional Issue

J

jlkirk

Active Member
Joined
May 6, 2002
Messages
328
Office Version
  1. 365
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
ABCDEFG
1A
2
3DateJanFebMarAprMayJun
41 1,790 1,385 1,870 1,840 1,489 1,835
52 1,694 1,572 2,191 1,751 1,489 1,393
63 1,630 1,929 2,005 1,632 1,491 1,348
74 1,023 1,902 1,967 1,705 1,416 187
85 2,291 1,897 1,707 1,763 1,450 1,617
96 2,304 1,815 1,493 1,667 1,477 1,522
107 2,152 1,253 1,747 1,666 1,525 1,509
118 2,053 1,272 1,799 1,615 1,595 1,506
129 1,801 1,341 1,742 1,526 1,196 1,538
1310 1,479 1,242 1,785 1,582 1,200 1,492
1411 1,792 2,599 2,144 385 1,049 1,439
1512 1,963 2,242 1,858 - 2,076 1,433
1613 1,989 1,770 1,896 1,202 1,714 1,358
1714 1,912 1,301 1,317 465 1,626 1,289
1815 1,767 1,330 1,920 327 1,620 1,425
1916 1,690 1,430 1,950 2,040 1,615 1,303
2017 1,638 2,336 1,714 1,730 1,588 1,265
2118 1,459 2,121 1,861 1,626 1,501 1,132
2219 1,462 2,332 1,814 1,355 1,270 1,163
2320 2,425 2,176 1,765 1,535 1,325 1,186
2421 2,103 2,031 1,662 1,329 1,413 1,221
2522 1,525 1,916 1,760 1,391 1,296 1,225
2623 2,219 1,845 1,738 1,536 1,193 1,187
2724 1,632 1,721 1,745 1,573 1,047 1,203
2825 1,453 1,751 1,335 1,546 1,473 1,179
2926 2,265 1,488 1,765 1,308 1,556 1,223
3027 2,174 1,146 1,702 1,678 1,498 1,218
3128 1,606 1,868 1,678 1,571 1,468 1,093
3229 1,501---- 1,705 1,599 1,485 1,005
3330 1,962---- 1,807 1,606 1,560 1,044
3431 2,143---- 1,960---- 1,481----
A


Excel Workbook
ABCDEF
1ABCDE
21/1/2007
31/2/2007
41/3/2007
51/4/2007
61/5/2007
71/6/2007
81/7/2007
91/8/2007
101/9/2007
111/10/2007
121/11/2007
131/12/2007
141/13/2007
151/14/2007
161/15/2007
171/16/2007
181/17/2007
191/18/2007
201/19/2007
211/20/2007
221/21/2007
231/22/2007
241/23/2007
251/24/2007
261/25/2007
271/26/2007
281/27/2007
291/28/2007
301/29/2007
311/30/2007
321/31/2007
332/1/2007
342/2/2007
352/3/2007
362/4/2007
372/5/2007
382/6/2007
392/7/2007
402/8/2007
412/9/2007
422/10/2007
432/11/2007
442/12/2007
452/13/2007
462/14/2007
472/15/2007
482/16/2007
492/17/2007
502/18/2007
512/19/2007
522/20/2007
532/21/2007
542/22/2007
552/23/2007
562/24/2007
572/25/2007
582/26/2007
592/27/2007
602/28/2007
Sheet1


Thanks in advance.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
If you want a formula in B2 on your summary sheet:

=INDEX(A!$A$1:$M$32,MATCH(DAY($A2),A!$A$1:$A$32,FALSE),MATCH(TEXT($A2,"mmm"),A!$A$1:$M$1,FALSE))

copied down. If you copy across remember to change the sheet reference.
 
Upvote 0
Thanks Andrew, but I am confused. Where does column M enter the picture, and why don't you reference all 34 rows of the worksheet A-E data?
Thanks again.
 
Upvote 0
Column M is ther because I assumed you data went out until December. I also assumed that you data started in row on on worksheet A. If it starts on row 3 as in your post:

=INDEX(A!$A$3:$M$34,MATCH(DAY($A2),A!$A$3:$A$34,FALSE),MATCH(TEXT($A2,"mmm"),A!$A$3:$M$3,FALSE))
 
Upvote 0
Thanks. How about making the positive values from worksheets B, D & E negatives on Sheet 1? Also, is there any additional argument that we coukld insert that would recognize the Worksheet name in the respective cells A1 and match them to the respective reference in row 1 of Sheet 1 so that I wouldn't need to change the reference in the formula as I copy over the columns in Sheet 1. As I said, there are quite a few, with various elaborate names (I used A-E for simplicity purposes)!
Thanks again.
 
Last edited:
Upvote 0
Andrew, please see edited reply to your post. Yes, I was aware of how to insert a negative sign before the index argument. However, I was more interested in a argument that said when I was "linking" the data the data from B, D & E over to sheet 1, they would be linked over as negatives: something along the lines of "If the data comes from B, D, or E, the -,+"...
 
Upvote 0
Like this?

=INDEX(A!$A$3:$M$34,MATCH(DAY($A2),A!$A$3:$A$34,FALSE),MATCH(TEXT($A2,"mmm"),A!$A$3:$M$3,FALSE))*IF(ISNUMBER(SEARCH(B$1,"BDE")),-1,1)
 
Upvote 0
Thanks. I'll give your proposed solution a shot. Should the argument be an OR(B,D,E)? Also, is there any additional argument that we could insert that would recognize the Worksheet name in the respective cells A1 and match them to the respective reference in row 1 of Sheet 1 so that I wouldn't need to change the reference in the formula as I copy over the columns in Sheet 1. As I said, there are quite a few, with various elaborate names (I used A-E for simplicity purposes)!
Thanks again.
 
Upvote 0
You can use INDIRECT:

=INDEX(INDIRECT("'"&B$1&"'!$A$3:$M$34"),MATCH(DAY($A2),INDIRECT("'"&B$1&"'!$A$3:$A$34"),FALSE),MATCH(TEXT($A2,"mmm"),INDIRECT("'"&B$1&"'!$A$3:$M$3"),FALSE))*IF(ISNUMBER(SEARCH(B$1,"BDE")),-1,1)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,616
Messages
6,125,865
Members
449,266
Latest member
davinroach

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top