Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Write formula for simple copy and paste to another cell
I have a worksheet that contains hundreds of columns. Each group of three are
related to each other (Cols A-C, D-F, etc...). I have this formula that works well for columns A-C =IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B $1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=CO LUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B $2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COL UMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B $2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A $3=$A$2,$B$2+1,1),IF(AND(COLUMN()=COLUMN($C$3),ROW ()=3),IF($A$3=$A$4,"",IF($A$3=$A$2,$B$3,1)),IF(COL UMN()=COLUMN(B2),IF(A2=A1,B1+1,1),IF(COLUMN()=COLU MN(C2),IF(A2=A3,"",IF(A2=A1,B2,1))))))))))) I know it is rather thick and probably difficult to read through, but that is my formula and it works exactly how I want......sort of. My problem is two-fold and I hope I say it correctly.... Column A contains a number (ex. 351), Column B counts the number of occurrences each particular instances is. Column C shows the last number of that occurrence. (Will provide example at the bottom) One, I need to be able to put this in cell A1 and copy it over and down to a variable number of columns and rows. Currently, I have to copy it exactly as shown above (not drag/copy) into cell B2, C2, B3, and C3, then I can do a drag copy of B3:C3 all the way down to the bottom of my data. Two, I can't use it in column A or in Row 1. (the cell references to col A and to row 1 causes #REF!). But I need it to know to look in cell A1 (or D1, or G1, etc...) My ultimate goal really is to just have one formula that I can put into cell A1 and can drag copy from top to bottom, left to right, without any manual changes. Worksheet example: Column A Column B Column C 722 1 1 351 1 351 2 351 3 3 879 1 879 2 2 151 1 1 744 1 1 Columns D-F would look similar but would have different numbers (Col A) in a different order. They are unrelated to Columns A-C. They are their own group of three, as would be G-I and so on. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
write a copy/paste from one workbook to another in a macro | Excel Discussion (Misc queries) | |||
How to write a simple copy-paste macro? | New Users to Excel | |||
no copy/cut and paste while being able still write | Excel Discussion (Misc queries) | |||
how do I write a formula to copy text from one cell to another? | Excel Worksheet Functions | |||
File write protection with copy/paste | Excel Discussion (Misc queries) |