PDA

View Full Version : Solved: Summarize data on one sheet based on format of a cell on another sheet???



infinity
09-16-2006, 10:26 PM
Hello VBaxers,

I have been elected to take on a project at work. I have a spreadsheet that I have developed, that calculates pallet quantities of foil for shipping on a truck. What I want to do is to be able to summarize the items selected on a second sheet based on the conditional formatting of one cell in the record of the first sheet. In other words, I have conditional formats set up, that if Range P16, for example, is greater than 0 then it highlights that entire row in one color and turns the font color to white.

Example: If any cell in range P14:P20, P24, P28:P32... ect. has a font color of white then I want to copy column C,D,E,P,M and J of that row or rows and paste them, in that order, in a summary section on another sheet. I want that data to go to the first unoccupied row in Range C25:P36 on the second sheet.

Or maybe it would be better to do it based on whether there is a value in those ranges?

Example: If any cell in range P14:P20, P24, P28:P32... ect. is >0 then (same as above)

I am trying to do this, if at all possible with formula's rather than macro's. My IT department would rather that I use formula's. They have told me that I can use macro's if I have to, but would rather that I limit them.

I have developed the sheet, and written several formula's and a few macro's for it, and I know what I want the spreadsheet to do, but I am clueless how to get there from this point. If anyone can help, I would greatly appreciate it. Thank you so much, in advance!

Bob Phillips
09-17-2006, 02:42 AM
I would start off b y collecting the row numbers of the target data in the new sheet.

So select A1:An, where n is the largest row number you need to cater fro, and in the formula bar, add

=IF(ISERROR(SMALL(IF(Sheet2!P1:P20>0,ROW($A1:$A20),""),ROW($A1:$A20))),"",
SMALL(IF(Sheet2!P1:P20>0,ROW($A1:$A20),""),ROW($A1:$A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.

Then in B1, add

=IF(A1<>"",INDEX(Sheet2!C1:C20,A1),"")

and copy down.

Then repeat in new columns for the other columns to copy.

infinity
09-19-2006, 09:04 PM
I am sorry, I am self taught in Excel and VBA and I am not familiar with how array formula's work yet. In your formula, because my target data is broken up by rows that I do not want to copy, do I collect the target data in one range on the second sheet and refer to that in the formula? Also, do I replace "Sheet2" with the name of my second sheet, and the $A1:$A20 with the range of the target data that I collected on the second sheet? I appreciate your help with this as this is new to me.