Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Row Valus In Different Sheet

Posted on 2014-12-17
11
Medium Priority
?
219 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 31

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 53

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Author Comment

by:Naresh Patel
ID: 40506598
Hi

Got errorError screenThanks
0
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 200 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 31

Accepted Solution

by:
gowflow earned 1800 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 31

Expert Comment

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

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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