Extract all data from Sheet1 Based on 1 Cell

M

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
42
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Any guesses?

mohdamir1989 said:
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
Now I'm bit stuck with references to month (Validated list in T column)
Click to expand...
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".
 
Upvote 0
Joe4 said:
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".
Click to expand...

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
 
Upvote 0
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:
Upvote 0
Joe4 said:
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?
Click to expand...

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:
Upvote 0
Instead of just headings, can you post some actual data?
Maybe then it will make more sense to us.
 
Upvote 0
Joe4 said:
Instead of just headings, can you post some actual data?
Maybe then it will make more sense to us.
Click to expand...

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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Copy & Paste Specific cells from a Row based on Drop-Down List
Replies
3
Views
2K
mumps
mumps
N
  • Question
Adding Data from an Excel Form
Replies
7
Views
1K
NDMDRB
N
N
  • Question
Join data from second worksheet with delimited list values (mults) to another worksheet with one value per cell/row and keep 1:1 relationship
Replies
2
Views
1K
HeadAgainstABrickWall
H
M
  • Question
Sorting Data from Multiple Pages by Date Based on Description
Replies
1
Views
572
MickG
M
M
  • Question
Sorting Data from Multiple Pages by Date Based on Category and Subcategory
Replies
6
Views
732
MickG
M

Forum statistics

Threads
1,214,649
Messages
6,120,731
Members
448,987
Latest member
marion_davis

Share this page

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
Back
Top