Solved

Row Valus In Different Sheet

Posted on 2014-12-17
11
213 Views
Last Modified: 2014-12-19
Hi Experts,

Need a help to create excel formula which populate row values of particular name in different sheet.

See attached, in sheet 1 column A is name column and values in subsequent columns & in sheet result cell A1 is matching criteria i.e. populate row values which have name Cell A1 in sheet 1. I know how to use filters but is there any way to achieve this by formula or worst case VBA.

Thanks
EE-Sample.xlsx
0
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40506434
ok some questions

1) You want to be able to input manually Cell A1 and for the formulas to display all the data concerning this cell ?
2) If 1 not correct please state what you want exactly as far as input and what is required as formulas.

gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40506487
1St statement is correct, I will manually put value in sheet result cell A1 and formula will display all data concerning to that cell.

Thanks
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 40506587
Hi,

you could use MS Query

see example

to refresh Goto Data / Connections /  Refresh All

Regards
Copy-of-EE-Sample.xlsx
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Author Comment

by:Naresh Patel
ID: 40506598
Hi

Got errorError screenThanks
0
 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 50 total points
ID: 40506644
In Data Connections Connections Properties Definition

Connection String
replace file name and path where bolded

DSN=Excel Files;DBQ=\\chca6037.eur.beluni.net\a853725$\Documents\EE\Copy of EE-Sample.xlsx;DefaultDir=\\chca6037.eur.beluni.net\a853725$\Documents\EE;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
0
 
LVL 30

Accepted Solution

by:
gowflow earned 450 total points
ID: 40506647
Here is a VBA solution.

Type in anything in Sheet result in Cell A1 and check the results. Make sure you activate macros. It is not CAPS sensitive like a = A we can make it caps sensitive.

Here is the code

Sub GetData()
Dim WS As Worksheet
Dim WS1 As Worksheet
Dim MaxRow As Long, I As Long, J As Long

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Set WS = ActiveSheet
Set WS1 = Sheets("sheet1")
MaxRow = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row

WS.Range("B1:" & WS1.Cells(MaxRow, WS1.UsedRange.Columns.Count + 1).Address).ClearContents

'---> Copy Header
WS1.Range("A1:" & WS1.Cells(1, WS1.UsedRange.Columns.Count).Address).Copy WS.Cells(1, "B")
J = 2

For I = 2 To MaxRow
    If LCase(WS1.Cells(I, "A")) = LCase(WS.Cells(1, "A")) Then
        WS1.Range("A" & I & ":" & WS1.Cells(I, WS1.UsedRange.Columns.Count).Address).Copy WS.Cells(J, "B")
        J = J + 1
    End If

Next I


With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Open in new window



Pls see attached wb,

gowflow
EE-Sample.xlsm
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 40507132
Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40507951
May I ask new question?
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40508178
You are free to ask for sure
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40508737
Did you want to ask something ?
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40509445
here is the link for new question.



Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

734 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