Quote:
|
Thanks GoBow777. Your example is very helpful, but when I was testing it, Sheet1 stopped reading items after row 30 in Sheet2. How can I change it so that Sheet2 will read up to 3000 rows in Sheet1. Secondly, how do I add 20 more rows in Sheet2. Lastly, is there a way not to have the purple fill ins? Thanks
|
rldjda:
Keep in mind this idea is not really meant for large groups of data because it could cause your computer to slow and or lockup.
The purple fill-ins are a result of Conditional Formatting, to remove them click Format/Conditional Formatting/Clear Rules/Clear Rules from Entire Sheet, you should do this on both sheets.
I have to assume that you meant to say, how do I add 20 more columns? If that’s the case then lets assume your range of data is A4:X3000.
If you know how many different or unique JOBID’s there are, then on Sheet2 paste this formula in cell AA4 and copy down as far as needed, but if your not sure then copy down to row 3000.
Code:
=IF(AB4="","",OFFSET(Sheet1!$A$1,AB4-1,0))
Paste this formula in cell AB4 and copy down to match the range of column AA.
Code:
=IF(OR(ISERR(SMALL(AC:AC,ROW(1:1))),Sheet1!A4=""),"",MID(SMALL(AC:AC,ROW(1:1)),FIND(".",SMALL(AC:AC,ROW(1:1))),6)*100000)
Paste this formula in cell AC4 and copy down to row 3000.
Code:
=IF(OR(Sheet1!A4="",COUNTIF(Sheet1!$A$4:$A4,Sheet1!$A4)1),"",COUNTIF(Sheet1!$A$4:$A$3000,"<"&Sheet1!$A4)+1+ROW()/100000)
Paste this formula in cell AD4 and copy down to row 3000.
Code:
=IF(Sheet1!A4="","",MID(SMALL(AE:AE,ROW(1:1)),FIND(".",SMALL(AE:AE,ROW(1:1))),6)*100000)
Paste this formula in cell AE4 and copy down to row 3000. The reference to cell $A$1 is the JOBID drop down button, change the reference to whatever cell you want to place the drop down button.
Code:
=IF(OR(Sheet1!A4="",Sheet1!A4<$A$1),"",COUNTIF(Sheet1!$A$4:$A$3000,"<"&Sheet1!$A4)+1+ROW()/100000)
Select the range A3:X3000 and press the delete key to clear out all the formulas and labels, then paste this formula in cell A4 and copy down to row 3000. Column A is the cell location for the JOBID in question.
Code:
=IF(ISNUMBER(AD4),"A"&AD4,"")
At this point you should probably save your workbook.
Paste this formula in cell B4 and copy down and across to cell Y3000. Label row 3 however you see fit.
Code:
=IF(ISERR(OFFSET(Sheet1!A$1,$AD4-1,0)),"",IF(OFFSET(Sheet1!A$1,$AD4-1,0)=0,"",OFFSET(Sheet1!A$1,$AD4-1,0)))
Select the cell you chose for your JOBID drop down button, (as previously discussed) then click Data/Data Validation, the Data Validation dialog box will open, where it says Allow: click the arrow button and select List, in the Source: box type in this formula and click the OK button.
Code:
=OFFSET(AA4,0,0,COUNT(AB:AB),1)
HTH