Solved

Row Valus In Different Sheet

Posted on 2014-12-17
11
199 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:itjockey
  • 5
  • 4
  • 2
11 Comments
 
LVL 29

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:itjockey
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 49

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
 
LVL 8

Author Comment

by:itjockey
ID: 40506598
Hi

Got errorError screenThanks
0
 
LVL 49

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

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:itjockey
ID: 40507132
Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40507951
May I ask new question?
0
 
LVL 29

Expert Comment

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

Expert Comment

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

Author Comment

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



Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now