Solved

Row Valus In Different Sheet

Posted on 2014-12-17
11
210 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 50

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:itjockey
ID: 40506598
Hi

Got errorError screenThanks
0
 
LVL 50

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

735 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