Extract all data from Sheet1 Based on 1 Cell

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Hi

I am trying to pull data from other sheet based on single cell (Month) reference. Just like a list in one cell. Sheet 1 contains multiple entries segregated with month column.

I need it all in a different format which I need to grab with single validated list.

Any possibilities??

Sheet 1 Format

Order Number

<tbody>
</tbody>
Equipment number/Bar Code

<tbody>
</tbody>
Material code

<tbody>
</tbody>
Parts Qty/ Amount

<tbody>
</tbody>
Part Name/ Material description

<tbody>
</tbody>
Feedback Date

<tbody>
</tbody>
St Loc / CCBPL

<tbody>
</tbody>
Sp Stk

<tbody>
</tbody>
Valuation Type

<tbody>
</tbody>
Price

<tbody>
</tbody>
Month

<tbody>
</tbody>
Amount

<tbody>
</tbody>
Station

<tbody>
</tbody>

<tbody>
</tbody>

Data to be processed at Sheet 2

S.NoComplaint#Order NoDateShop NameAsset codeFault& RamedyPart Change NameLevelTerritoryAmountRemarksAgencyComplaint Fwd DateClosing DateTotal DaysTotal HoursManual LevelMonthJan

<tbody>
</tbody>

Only to pick available information upon Month change.

Best Regards
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Any guesses?

Hi

I am trying to pull data from other sheet based on single cell (Month) reference. Just like a list in one cell. Sheet 1 contains multiple entries segregated with month column.

I need it all in a different format which I need to grab with single validated list.

Any possibilities??

Sheet 1 Format

Order Number

<tbody>
</tbody>
Equipment number/Bar Code

<tbody>
</tbody>
Material code

<tbody>
</tbody>
Parts Qty/ Amount

<tbody>
</tbody>
Part Name/ Material description

<tbody>
</tbody>
Feedback Date

<tbody>
</tbody>
St Loc / CCBPL

<tbody>
</tbody>
Sp Stk

<tbody>
</tbody>
Valuation Type

<tbody>
</tbody>
Price

<tbody>
</tbody>
Month

<tbody>
</tbody>
Amount

<tbody>
</tbody>
Station

<tbody>
</tbody>

<tbody>
</tbody>

Data to be processed at Sheet 2

S.NoComplaint#Order NoDateShop NameAsset codeFault& RamedyPart Change NameLevelTerritoryAmountRemarksAgencyComplaint Fwd DateClosing DateTotal DaysTotal HoursManual LevelMonthJan

<tbody>
</tbody>

Only to pick available information upon Month change.

Best Regards
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Hi,

After searching through web regarding the requirement, I found a very close solution in VBA. Now I'm bit stuck with references to month (Validated list in T column)

The code is basically extracting data based on static (Already defined in code) dates. I want the same to use for the month purpose

Code:
Sub extractDataBasedOnDate()
Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range(“A1”).Select
For i = 2 To lastrow
    mydate = Cells(i, 2)
[B]If mydate >= “14-mar-2012” And mydate <= “20-mar-2012”[/B] Then
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Range(Cells(i, 1), Cells(i, 2)).Copy Destination:=Sheets(“sheet2”).Cells(erow, 1)
End If
Next I
End Sub

Thanking in advance.

Best Regards
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Now I'm bit stuck with references to month (Validated list in T column)
Where exactly are these months coming from?
Is it column T of the same row where "mydate" is coming from?
That is only one date, however. What is the other date? Is it always 6 more than the first date?
You should be able to reference them in the code, just like you are referencing cells for "mydate".
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Where exactly are these months coming from?
Is it column T of the same row where "mydate" is coming from?
That is only one date, however. What is the other date? Is it always 6 more than the first date?
You should be able to reference them in the code, just like you are referencing cells for "mydate".

Dear Joe,

Thank you for your kind response.

This is base data in sheet named "Material Consuption"
Data in Base sheet can be anywhere from 1st (A:M) Month to be considered for is in "K" Column

Complaint #
(Column A)
Equipment number/Bar CodeMaterial codeParts Qty/ AmountPart Change NameFeedback DateSt Loc / CCBPLSp StkValuation TypePriceMonthAmountService Center
(Column M)

<tbody>
</tbody>

Data to be extracted on Sheet named "Billing" has following format

S.No
(Column A)
Complaint#Order NoDateShop NameAsset codeFault& RamedyPart Change NameLevelTerritoryAmountRemarksAgencyComplaint Fwd DateClosing DateTotal DaysTotal HoursManual LevelMonthJan
(Column T)

<tbody>
</tbody>

Highlighted cells are match in both sheets

Thank you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I don't think that answers the questions I asked.

I was offering to help you with the VBA code that you said almost works, but needs to be adjusted for month references.
Can you please specifically answer the questions I asked in my previous response?
 
Last edited:

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
I don't think that answers the questions I asked.

I was offering to help you with the VBA code that you said almost works, but needs to be adjusted for month references.
Can you please specifically answer the questions I asked in my previous response?

Where exactly are these months coming from?
Is it column T of the same row where "mydate" is coming from? (No MyDate is in Sheet1)
That is only one date, however. What is the other date? Is it always 6 more than the first date? (No, it is a full column having data from Dec 2017 till date and so on)
You should be able to reference them in the code, just like you are referencing cells for "mydate". ( I didn't write this code, I am just trying to learn as I mentioned "After searching through web regarding the requirement, I found a very close solution in VBA"


 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
Instead of just headings, can you post some actual data?
Maybe then it will make more sense to us.
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Instead of just headings, can you post some actual data?
Maybe then it will make more sense to us.

Dear Joe,

Below is actual data, Month is extracted from date using (=text(cellref,"mmm")

Sheet name (Material Consuption)

I just need to extract data based on Validated list (Column T) in next sheet. If the month changes all the data to related month must be extracted.


ABCDEFGHIJKLM
Order NumberEquipment number/Bar CodeMaterial codeParts Qty/ AmountPart Name/ Material descriptionFeedback DateSt Loc / CCBPLSp StkValuation TypePriceMonthAmountService Center
432007300129869306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432009300204495306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432017300273446306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432018300008058306-990-5911COMPLAINT HANDLING SERVICE WARRANTY22-Oct-17 0.01Oct0.01Center B
432020300376550306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432026300229764306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432027300446307306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432031300293920306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432038300676611306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432044300010056306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432059300005141306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432062300368729306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432063300367512306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432064300271624306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432066300366997306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432074300246216306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432080300352533306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432083300600077306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432125300324218306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432153300313081306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432156300131614306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432159300013229306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432222300354393306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432231300336855306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432242300359989306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432244300439463306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432263300263331306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432268300157558306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432271300157542306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432275300013669306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432277300157723306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432280300454367306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432282300662065306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432628300032991306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432364300134045306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432367300134045306-990-5911COMPLAINT HANDLING SERVICE WARRANTY23-Oct-17 0.01Oct0.01Center B
432371300619240306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432411300202780306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432498300679209306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432609300369761306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432611300334255306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432615300613283306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432618300394026306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 

Forum statistics

Threads
1,144,280
Messages
5,723,470
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top