Solved

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

Posted on 2011-03-03
22
604 Views
Last Modified: 2012-06-21
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
Comment
Question by:tnilesh2
  • 10
  • 5
  • 4
  • +1
22 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35033525
Question - is the Summary sheet a log, or must it overwrite what is already there?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35033996
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35035204
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35035433
@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
 
LVL 41

Expert Comment

by:dlmille
ID: 35040808
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
 

Author Comment

by:tnilesh2
ID: 35041926
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
 

Author Comment

by:tnilesh2
ID: 35042973
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35042974
We certainly can.  I just happened to be up.  I'll check on the error and your suggested change.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35042975
PS - are you running Excel 2003 or 2007, please?

Thanks!

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 35042990
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35043007
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35043015
And easy to follow
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35043026
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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 35043031
Correction

When run from the summary sheet

should read

When not run from the summary sheet
0
 
LVL 80

Expert Comment

by:byundt
ID: 35052873
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35052992
@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
 
LVL 80

Expert Comment

by:byundt
ID: 35053271
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35058467
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35059175
@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
 
LVL 80

Expert Comment

by:byundt
ID: 35059356
Dave,
If you activate each worksheet, you've got it handled.

Brad
0
 

Author Comment

by:tnilesh2
ID: 35273925
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
 

Author Closing Comment

by:tnilesh2
ID: 35273931
Thanks.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now