Shape & Slicer

[Nrpatel]

Help Me on this Please

 

[Asheesh]

Welcome to the forum..

So if I am right, no two sheets will have the same bill no

 

[Nrpatel]

Yes All Bill No. Different, according to month and serial number

 

[Asheesh]

Check this one...it should help

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim srch As String
Dim wsht As Worksheet
Dim isht As Worksheet
Dim lRow As Long
Dim DataRow As Long
   
Set isht = Sheets("Invoice")

If Not Intersect(Target, Target.Worksheet.[(E2)]) Is Nothing Then
   
    srch = [(E2)].Value
   
    For Each wsht In ThisWorkbook.Worksheets
   
        If wsht.Name = isht.Name Then
       
            GoTo NextSheet
       
        Else
       
            lRow = wsht.Cells(Rows.Count, "A").End(xlUp).Row
   
            wsht.Range("A2:I" & lRow).AutoFilter Field:=9, Criteria1:=srch
           
           
            If wsht.AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
           
            DataRow = wsht.Range("A2").End(xlDown).Row

                    isht.Range("B2").Value = wsht.Cells(DataRow, "D")
                   
                    isht.Range("E3").Value = wsht.Cells(DataRow, "B")
                   
                    isht.Range("B6").Value = wsht.Cells(DataRow, "E")
                   
                    isht.Range("C6").Value = wsht.Cells(DataRow, "C")
                   
                    isht.Range("D6").Value = wsht.Cells(DataRow, "F")
                   
                    Exit Sub
                   
                End If
           
        End If
           
NextSheet:

    Next wsht
   
End If
   
End Sub

 

[Nrpatel]

Work with only one sheet for other work sheet its dont work
Work only November Sheet only
Please Fill with FORMULA not MICRO
For January to December Sheet
Please Help Me
Thanks

 

[Asheesh]

It worked fine for me when I wrote it, however, I shall revisit it soon.
Regarding the Formula solution I am not sure if it can be done with a single formula but I can try..

let me call upon other experts to comment - @NARAYANK991 @Luke M @Hui

 

[Luke M]

Asheesh is correct, a formula-only solution here will be near impossible due to bad workbook design. If you can change the design, I'd suggest using a single worksheet for all the data, rather than splitting it out by month. You already have a field that has the date in it, so there's no reason from data analyses viewpoint to have multiple worksheets.

 

[Luke M]

Here's how I'd change the design, and the subsequent formula solution. I modified your PivotTable so that you can easily switch between months/years.

 

[Asheesh]

@Luke M .. simple and pure example....how important is to plan a workbook..

 

[Nrpatel]

Yes, I Agree with u all expert but my problem id diff. because I need Month wise sheet. You do one thing do one master sheet combine all 12 month in one master sheet then pull data from master sheet to fill up invoice.
Tnks.
PLEASE refer My New Attachment

 

[Luke M]

What do you gain from having multiple month worksheets? It would be again be easier to start with all data in one location (and then split it out if needed) than trying to go the other way. It would also reduce overall file size since you only have one PivotTable as opposed to twelve.

Nevertheless, here's your file with a macro taken from Ron's site that will consolidate all the month's into the Master sheet. Then your formulas can be based off of the master sheet.

 

[Nrpatel]

Thank You Very Much Ninjas Specially LUKE M
Tnks Agn

 

[Nrpatel]

Thank You Very Much Ninjas Specially LUKE M
january month sr. no. 1 is not in Master Sheet pls. FIX this
Tnks Agn

 

[Luke M]

Ah. Should have put in the header first, then copy data. Swapping things around...

 

[Nrpatel]

Thank You For All Things

 

[Nrpatel]

in my attachment all month serial no. from 1 to 9 show in bill no. 5 digit i want it in 6 digit like 150101 that show like this 15011 in january month.
Please Help

 

[Asheesh]

Use the below

TEXT(B2,"yymm")&TEXT(A2,"00")

 

[Nrpatel]

Thank You Very Much Expert

 

[Nrpatel]

Is Any other Method for serial Numbering u have Please Share with me. i want other metho for serial numbering for other work.

 

[Asheesh]

What is wrong with this method..And I am not sure about your exact requirement, however, follow the below steps to generate the sequential numbers

Define Name sNum as
=MAX($A$1:$A1)+1
then in cell A2 type =sNum
Now, drag it down..
Note: Do not enter the formula in A1..start from cell A2 otherwise it will give the circular ref.

 

[Nrpatel]

Any other Trick For Generate Bill Number

 

[Khalid NGO]

Any other Trick For Generate Bill Number

Hi @Nrpatel,
I am not sure you are looking for this:
in I2 simply refer to column B:
=B2

set custom format:
yymmdd

copy down...

Regards,

PS.
1) I have removed your several duplicate posts in this thread, please do not post duplicate.
2) If someone provided you the solution, give him proper feedback.

 

[vivekgoyal]

Hi @Nrpatel ,
Although I know experts have provided some wonderful solutions to your initial requirement, but here is an attempt from me, please check. This doesn't need a master sheet to be created.
Best,
Vivek

 

[Nrpatel]

Thank You Very Much Vivek Goyal

 

[Nrpatel]

Help For Total Interest to pay
PLEASE refer My New Attachment