Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

Excel - Gather data from different sheet & arrange in summary sheet

Dear Experts,
As shown in attached file, we are monitoring the stock of different type of materials, If the stock level falls below a certain limit then the color of cells of column "F" becomes "Yellow" further fall of stock level converts the color of cell to "Pink". There may be 15 to 20 sheets in this file.

We can monitor the stock level by analyzing each sheet separately.

Now we want that - If the color of cell of column "F" is "Yellow or Pink" then the entire row should be copied & pasted in Summary sheet. The format of Summary sheet is given in attached file.

Please suggest the macro codes to solve the purpose,

Thanks in advance.

Nilesh.
Example.xls
0
tnilesh2
Asked:
tnilesh2
  • 10
  • 5
  • 4
  • +1
2 Solutions
 
dlmilleCommented:
Question - is the Summary sheet a log, or must it overwrite what is already there?

Dave
0
 
dlmilleCommented:
Also, what version of Excel are you using? It appears you are using format conditions to set the colors and the way this works varies based on version of Excel.

Can you run this in Excel 2007, or do you only have Excel 2003?

Thanks!

Dave
0
 
Saqib Husain, SyedEngineerCommented:
Try this macro

Saqib
Sub gathershort()
rn = 5
Set summ = ThisWorkbook.Sheets("Summary")
summ.Range(Replace(summ.UsedRange.Address, Left(summ.UsedRange.Address, InStr(summ.UsedRange.Address, ":")), "A6:")).Clear
For Each ws In ThisWorkbook.Sheets
    summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium
    If ws.Name <> "Summary" Then
        For Each rw In ws.UsedRange.Rows
            If rw.Row > 1 Then
                If WorksheetFunction.IsNumber(rw.Cells(1, 1)) Then
                    If rw.Cells(1, 6) <= rw.Cells(1, 5) Then
                        rn = rn + 1
                        Cells(rn, 2) = Right(ws.Name, Len(ws.Name) - 5)
                        Cells(rn, 3) = taip
                        Cells(rn, 2).Borders(xlEdgeLeft).LineStyle = xlContinuous
                        Cells(rn, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
                        Cells(rn, 2).Borders(xlEdgeLeft).Weight = xlMedium
                        Cells(rn, 3).Borders(xlEdgeLeft).Weight = xlThin
                        For i = 4 To 9
                             rw.Range("b1:g1").Copy Cells(rn, 4)
                        Next i
                        summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlThin
                                                Cells(rn, 2).Borders(xlEdgeLeft).Weight = xlMedium
                        Cells(rn, 9).Borders(xlEdgeRight).Weight = xlMedium
                    End If
                Else
                    taip = rw.Cells(1, 1)
                End If
            End If
        Next rw
    End If
Next ws
summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium
End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
@ssaqibh - quite innovative and simple and I might have gone that route, but I figured there could be more intensive logic to Nilesh's conditional formatting than just managing inventories - especially for future use.  However, I went at it a different way. Would have had it posted over an hour ago but just as I was putting finishing touches on it and testing in multiple Excel versions, I crashed my file without a backup!  Had to recode from memory...

@Nilesh  - I took your request literally - rather than back-interpret your conditional formatting, I took you at your word - you wanted PINK cells and YELLOW cells moved over.  Whether you conditionally format PINK or YELLOW or you actually FILL the cells with PINK or YELLOW, this will do the trick.

See Control Panel for additional instructions on adding additional colors to the mix.

I leveraged byundit's approach and the function ConditionalColor to retrieve the actual color index of each of the F cells in every tab.  I had to modify the code to work in 2007 (it was poted when only 2003 was available) and logic has changed with Excel with respect to how this function works.

Suffice it to say, I've done a bit of testing to ensure it works.  If you have ANY problems, feel free to buzz me - even after this question is closed.  It was a lot of fun learning how conditional formatting works underneath the covers with VBA!

Whether you award ssaqibh or me or both, I'm glad I had the opportunity to work this and build a new tool that looks at conditional formatting.

Cheers,

Dave
Example-Data-v1.xls
0
 
dlmilleCommented:
Nilesh - please advise if you've tried these submissions.  As noted in my above post 35035433, I put quite a bit of effort into reading your colors and interpreting conditional formatting and would be very happy to ensure this works well for you!

Dave
0
 
tnilesh2Author Commented:
Dear Saqib / Dave,

Thank you very much for your efforts & Sorry for late reply.

I am checking the files & will revert in case of any query.

Nilesh.
0
 
tnilesh2Author Commented:
Dear Dave,

I tested your files & found OK except in following case -
If in a sheet if there is not even a single cell with "yellow / pink" color then file is giving error of "Out of Range".

One more thing that is new - can we modify / run these codes if the "Type #" is not there & some comodity name is there as "Ink", "Pen", "BOX" etc. given in sheet "Data n".
Please look in to the same.


Thanks,
Nilesh.
Example-Data-v1-1.xls
0
 
dlmilleCommented:
We certainly can.  I just happened to be up.  I'll check on the error and your suggested change.

Dave
0
 
dlmilleCommented:
PS - are you running Excel 2003 or 2007, please?

Thanks!

Dave
0
 
dlmilleCommented:
Ok - here you go.  I fixed my bug with no colors on a page, and I searched in column A upward for a non-numeric (like Type or Ink or whatever) and when found, that's what I used as the Type No.

Here is the revised solution...

Dave
Example-Data-v2.xls
0
 
Saqib Husain, SyedEngineerCommented:
What about my macro? It came in first and did not have any of the issues you mentioned. And will run on any version.

Saqib
0
 
Saqib Husain, SyedEngineerCommented:
And easy to follow
0
 
Saqib Husain, SyedEngineerCommented:
One issue it has is that it gives and error when run from the Summary sheet.

To correct it change the line 6

    summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium

to

    summ.Range(summ.Cells(rn, 2), summ.Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium

0
 
Saqib Husain, SyedEngineerCommented:
Correction

When run from the summary sheet

should read

When not run from the summary sheet
0
 
byundtCommented:
It is worth noting that Excel 2010 added the DisplayFormat property. This allows you to for once and for all answer the question of what color is a cell (although you can't use it in a UDF called by a worksheet formula):
MsgBox Worksheets("Some sheet").Range("A1").DisplayFormat.Interior.ColorIndex    'Returns conditional format color (if active), regular color otherwise

Open in new window

Brad
0
 
dlmilleCommented:
@byundt - Excellent -  the code would become much simpler in future applications when we don't have to worry about backward compatibility, either.

Thanks for the input and I'll note it on my solution for future use.  For this solution, I believe I've trapped all that needs to be trapped for the Excel 97 and higher versions.

Cheers,

Dave
0
 
byundtCommented:
Dave,
I don't believe that the approach you chose will handle the completely general case. If the Conditional Formatting formulas on the other worksheet use relative addressing, they are returned relative to the active cell on that worksheet--and you have no idea what that active cell is because the worksheet isn't active.

Fortunately, in Nilesh' workbook this isn't the case. With absolute formulas or value comparisons, you don't need to know the active cell.

Brad
0
 
dlmilleCommented:
Correct - thanks.  As long as the conditions are using absolute or value formulas...

Nilesh - are you planning to use relative addressing, or just absolute/value addressing?  If this is working for you in all conditions you plan to use, please advise.

Dave
0
 
dlmilleCommented:
@byundit - I think I responded too soon, as when I reviewed the code, I found that I do activate
each sheet individually to assess the color conditions.

Recall, this particular question is calling for a Summary report to be generated, and the way its
generated is reviewing the current conditions in each of the sheets.

My process is to activate each sheet individually before calling the conditionalcolor routine
which had your documentation that the sheet must be active for the "formula is" to return correct
results.

So, I think I've got that handled, do I not?  I created a relative reference condition and seems to work just fine...

Dave
0
 
byundtCommented:
Dave,
If you activate each worksheet, you've got it handled.

Brad
0
 
tnilesh2Author Commented:
Sorry guys for late reply,
I was away from my home town. Closing this thread herewith.
Really not tried solution of byundt, as the solution given by dlmille & ssaqibh did work for me.

Thanks  byundt, dlmille & ssaqibh.
0
 
tnilesh2Author Commented:
Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now