| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to map cells to a reference then copy adjacent ones
It worked! Thanks Jacob, you are the best
"Jacob Skaria" wrote: You can. Change the array reference in the formula Sheet1!$A:$K to whatever..Say Sheet1!$A1:$CV100. Try and feedback If this post helps click Yes --------------- Jacob Skaria "Dream" wrote: Thanks alot Jacob this really works! But how can I apply it to more than 9 columns...becuase I am trying to make it extend to across around 100 columns or more...Thanks in advanvce "Jacob Skaria" wrote: Hi Dream Try the below With Sheet1 as below Col A Col B Col C Col D Col E Col F Project A 1 20 30 Project B 3 30 60 70 80 Project C 5 10 20 and Sheet 2 as below Col A Col B Col C Col D Col E Col F ... 1 2 3 4 5... Project A 20 30 Project B 30 60 70... Project C 10... Project D --Sheet2 Row 1 extends upto (Col J upto 9) --The index or project name in both sheets should be exactly same. In your example it is 'A' and 'Project A'. Both should be same as above. --In Sheet2 cell B2 apply the below formula and copy across to Col J and copy down as required =IF(ISNA(VLOOKUP($A2,Sheet1!$A:$K,2,0)),"",IF(VLOO KUP($A2,Sheet1!$A:$K,2,0)B$1,"",IF(VLOOKUP($A2,Sh eet1!$A:$K,2,0)=B$1,VLOOKUP($A2,Sheet1!$A:$K,3,0), IF(VLOOKUP($A2,Sheet1!$A:$K,B$1-VLOOKUP($A2,Sheet1!$A:$K,2,0)+3,0)=0,"",VLOOKUP($A 2,Sheet1!$A:$K,B$1-VLOOKUP($A2,Sheet1!$A:$K,2,0)+3,0))))) If this post helps click Yes --------------- Jacob Skaria "Dream" wrote: Hi all, I appreciate your help on the following. Please refer to teh example at the bottom. in the first sheet i have the following, A 1 20 B 3 30 60 C 5 10 20 in the second sheet i have, 1 2 3 4 5 6 7 8 9 i want excel to distribute the information above into the second sheet, so that the result will be in sheet 2 to be as 1 2 3 4 5 6 7 8 9 project A 20 project B 30 60 project C 10 20 please note that the idea is that for each project in sheet1, copy the entire row to sheet2 starting from the location where the numbers in the second column match the numbers in sheet2. For example, for project A, the number is 1, so copy the entire row (in this case 20) to the location under 1 in sheet2. for project B, go to coloumn 3 in sheet 2, and copy the entire row (in this case 2 cells 30 and 60) into adjacent cells in sheet2 starting at column 3, so that 30 will reside in column3, and 60 will reside in the column next to it which is column 4...and so forth... Thanks in advance |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I copy a combobox to many cells with relative reference? | Excel Discussion (Misc queries) | |||
| When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
| Copy without reference cells | Excel Discussion (Misc queries) | |||
| How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
| How can I autofill a series to reference non adjacent cells? | Excel Discussion (Misc queries) | |||
Threaded Mode
