Solved

Row Valus In Different Sheet

Posted on 2014-12-17
11
198 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 48

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 48

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 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