XL help required

Solved/Closed

shailendra_arya2006

Posts: 3
Registration date: Tuesday July 26, 2011
Status: Member
Last seen: October 28, 2011

- Jul 27, 2011 at 04:37 PM
shail - Aug 7, 2011 at 10:21 AM

Hello,
I need a help for one of my workbook.
I have a workbook with a sheet name master and contains the below data:

Nokia LG MOTOROLA SONY HTC
DELHI 22 32 12 12 33
Mumbai 11 22 33 66 65
Guwahati 22 32 12 12 33
shimla 45 66 89 102 45
pune 47 85 92 18 11
bareilly 22 32 12 12 33

I have three more sheets in that with name shonu,mohan and raj.

Raj Data:
Nokia LG
DELHI 22 32
shimla 45 66
bareilly 22 32

Shonu data:
MOTOROLA SONY HTC
Guwahati 12 12 33
pune 92 18 11
Mumbai 12 12 33

Shonu data:
LG MOTOROLA
DELHI 32 12
shimla 66 89
bareilly 32 12

I want a formula that picks the value from sheet 1 to other three depending on the store and phone model.The formula should check store and phone model simultaneously and then display the quantity. In case i change the headers in sheet shonu to Motorola LG from Motorola LG then qty should be changed automatically.

I really need this help urgently and will be really thankful if anyone suggest me the required formula.

Thousands of thanks in Advance.

Regards
Shail

XL help required
Msi app player system requirements - Download
A considering that organisation requires communication today office automation is no longer limited to simply capturing handwrit - Articles
Asistencia472.com this requires seriousness and an account registered in your name that will help you with these procedures - Forum - Windows
Lunar client system requirements - Download
Google earth system requirements - Download

1 reply

Answer 1 / 1

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Jul 28, 2011 at 03:44 PM

Could you upload a sample excel file with some sample data on some shared site and post back the link. In the file if you could show how the data is and how you would like it to be

shail
Jul 30, 2011 at 06:11 PM

Hi,,thanks for the reply... can u give me ur email id so that i can send a sample file.

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Jul 31, 2011 at 08:16 AM

Thanks a no no here. You can upload a file to a shared site and then post back the link to the file here.

shail
Jul 31, 2011 at 02:08 PM

http://shailendraisking.hpage.com/

please see the above link..this will redirect you to my site.Under main menu,find the introduction tab and click there. you will find XL Help required,clik on it and file will be open.

Thanks

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Aug 1, 2011 at 09:57 PM

Each time you paste data, would the previous data (all rows) in the other sheets will be updated ? or only few rows will be updated and rest should be left alone ?

shail
Aug 2, 2011 at 06:33 AM

this sheet is to be updated weekly,so data has to be updated in all sheets whenever there will be a change in master...

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Aug 6, 2011 at 01:31 PM

Try this. Main macro is doUpdateChild

You can fire it manually or you can call it with change event of master sheet like

Private Sub Worksheet_Change(ByVal Target As Range)
    call  doUpdateChild
End Sub

But you can call once data is pasted

Option Explicit

Public Sub doUpdateChild()
   Dim sMasterSheet              As String
   Dim lMasterStartRow           As Long
   Dim lMasterEndRow             As Long
   Dim iMasterStartCol           As Integer
   Dim iMasterEndCol             As Integer
   Dim sTransSheet               As String
   Dim lTransStartRow            As Long
   Dim lTransEndRow              As Long
   Dim iTransStartCol            As Integer
   Dim iTransEndCol              As Integer
   Dim wsSheet                   As Worksheet
   Dim sRowMatch                 As String
   Dim sColMatch                 As String
   
   sMasterSheet = "Master Sheet"
   lTransStartRow = 5
   iTransStartCol = 1
   
   With Sheets(sMasterSheet)
      lMasterStartRow = getItemLocation("*", .Cells, bLastOccurance:=False)
      lMasterEndRow = getItemLocation("*", .Cells)
      iMasterStartCol = getItemLocation("*", .Cells, bLastOccurance:=False, bFindRow:=False)
      iMasterEndCol = getItemLocation("*", .Cells, bFindRow:=False)
   End With
   
   For Each wsSheet In Worksheets
      sTransSheet = wsSheet.Name
      Select Case sTransSheet
         Case sMasterSheet, "PLACE HOLDER FOR OTHER SHEET"
            GoTo Next_wsSheet
            
         Case Else
            With wsSheet
               
               lTransEndRow = getItemLocation("*", .Range(.Cells(lTransStartRow, iTransStartCol), .Cells(.Rows.Count, .Columns.Count)))
               If (lTransStartRow >= lTransEndRow) _
               Then
                  MsgBox "No Location rows found in sheets " & sTransSheet
                  GoTo Next_wsSheet
               End If
               
               iTransEndCol = getItemLocation("*", .Range(.Cells(lTransStartRow, iTransStartCol), .Cells(lTransEndRow, .Columns.Count)), bFindRow:=False)
               If (iTransStartCol >= iTransEndCol) _
               Then
                  MsgBox "No Model column found in sheets " & sTransSheet
                  GoTo Next_wsSheet
               End If
               sRowMatch = "MATCH(RC" & iTransStartCol & ",'Master Sheet'!C" & iMasterStartCol & ":C" & iMasterStartCol & ",0)"
               sColMatch = "MATCH(R" & lTransStartRow & "C,'Master Sheet'!R" & lMasterStartRow & ":R" & lMasterStartRow & ",0)"
               With .Range(.Cells(lTransStartRow + 1, iTransStartCol + 1), .Cells(lTransEndRow, iTransEndCol))
                  .ClearContents
                  .FormulaR1C1 = "=INDIRECT(ADDRESS(" & sRowMatch & "," & sColMatch & ",,,""" & sMasterSheet & """))"
                  Application.CutCopyMode = False
                  .Copy
                  .PasteSpecial xlPasteValues
                  Application.CutCopyMode = False
               End With
            End With
      End Select
      
Next_wsSheet:
   Next
End Sub

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Aug 6, 2011 at 08:24 PM

As I said, if you want to auto execute the macro then you have to define the routine

Private Sub Worksheet_Change(ByVal Target As Range)
    call  doUpdateChild
End Sub

For that, you would
1. press ALT + F11
2. press CTRL + R
3. Double click on your master sheet in the project explorer window
4 paste the above three line.

This would result in that if you make any change in the master sheet, the macro will get executed. This is potentially not so good, as if I am updated 4 cells, the macro will get executed 4 times. So you would be better off firing the macro on your own

About change of header, it is not expecting any change in header row and fixed column 1
If you add a make any change to the model, or location, you need to do that manually. Like if you add a new model to master sheet, then you would have to add that model to one to the sheets yourself. Or if you move a model from one sheet to other, that too you have to do it yourself. Which make sense as a program cannot know what models and location goes to what sheet.

So I would suggest that you run the macro on your own when you are ready to run it. From Excel, you can run by going to Tool - macro and select the macro to run (or do via ALT + F11 route or create a short cut)

Subscribe To Our Newsletter!