Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jarviscars
 
Posts: n/a
Default Return cell contents based on conditional lookup


I'm trying to create a formula to lookup the master sheet, find all
occurances of the loaction and return the values in the location sheet.
(sample outlined below...)

I HAVE A MASTER SHEET WHICH CONTAINS DATA IMPORTED VIA A CSV FILE

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7


I THEN HAVE SEPARATE SHEETS FOR EACH LOCATION

Sheet 1 = Location A
Sheet 2 = Location B
Sheet 3 = Location C

THEN EACH LOCATION SHEET WILL ONLY SHOW THE DATA FOR THE ROWS WHERE THE
VALUE IN COL B = THE LOCATION.

Therefo

*Sheet 1 *

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4

*Sheet 2 *

Row 1 = 10003 || Location B || Brand 4 || Model 8
Row 2 = 10005 || Location B || Brand 3 || Model 7

*Sheet 3 *

Row 1 = 10004 || Location C || Brand 2 || Model 2

Any help is appreciated...


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #2   Report Post  
Derek Y via OfficeKB.com
 
Posts: n/a
Default


I can't think of a way to do this with IF functions without getting stuck
with a lot of false returns in each of the location sheets. If i were you i
would just select the row 1 in your master cheet, go to data, filter, then
select auto filter. Now you can click on location and scroll down to
whichever location you want, and you'll only see that data.

Sorry i couldn't be of more help.

Derek

jarviscars wrote:
I'm trying to create a formula to lookup the master sheet, find al
occurances of the loaction and return the values in the location sheet
(sample outlined below...)

I HAVE A MASTER SHEET WHICH CONTAINS DATA IMPORTED VIA A CSV FIL

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7

I THEN HAVE SEPARATE SHEETS FOR EACH LOCATIO

Sheet 1 = Location A
Sheet 2 = Location B
Sheet 3 = Location C

THEN EACH LOCATION SHEET WILL ONLY SHOW THE DATA FOR THE ROWS WHERE TH
VALUE IN COL B = THE LOCATION

Therefo

*Sheet 1 *

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4

*Sheet 2 *

Row 1 = 10003 || Location B || Brand 4 || Model 8
Row 2 = 10005 || Location B || Brand 3 || Model 7

*Sheet 3 *

Row 1 = 10004 || Location C || Brand 2 || Model 2

Any help is appreciated..



--
Message posted via http://www.officekb.com
  #3   Report Post  
Morrigan
 
Posts: n/a
Default


Assume A1:D1 is the header row on all 4 sheets(Master, LocationA,
LocationB, and LocationC), and data starts on row 2.

On sheet LocationA:
A2 =
INDEX(Master!$A$1:$D$6,SUMPRODUCT(SMALL(ROW(Master !$2:$6)*(Master!$B$2:$B$6="Location
A"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Location
A")+ROW()-1)),COLUMN()) (Copy across and down)

Similarly on sheet LocationB and LocationC:
A2 =
INDEX(Master!$A$1:$D$6,SUMPRODUCT(SMALL(ROW(Master !$2:$6)*(Master!$B$2:$B$6="Location
B"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Location
B")+ROW()-1)),COLUMN()) (Copy across and down)

A2 =
INDEX(Master!$A$1:$D$6,SUMPRODUCT(SMALL(ROW(Master !$2:$6)*(Master!$B$2:$B$6="Location
C"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Location
C")+ROW()-1)),COLUMN()) (Copy across and down)


Hope this helps.



jarviscars Wrote:
I'm trying to create a formula to lookup the master sheet, find all
occurances of the loaction and return the values in the location sheet.
(sample outlined below...)

I HAVE A MASTER SHEET WHICH CONTAINS DATA IMPORTED VIA A CSV FILE

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7


I THEN HAVE SEPARATE SHEETS FOR EACH LOCATION

Sheet 1 = Location A
Sheet 2 = Location B
Sheet 3 = Location C

THEN EACH LOCATION SHEET WILL ONLY SHOW THE DATA FOR THE ROWS WHERE THE
VALUE IN COL B = THE LOCATION.

Therefo

*Sheet 1 *

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4

*Sheet 2 *

Row 1 = 10003 || Location B || Brand 4 || Model 8
Row 2 = 10005 || Location B || Brand 3 || Model 7

*Sheet 3 *

Row 1 = 10004 || Location C || Brand 2 || Model 2

Any help is appreciated...



--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #4   Report Post  
jarviscars
 
Posts: n/a
Default


Thanks Morrigan... but that seems to be returning any value of the same
row in the master sheet.

I found a sample workbook by Debra Dalgleish which used macros and
filters to create sheets dynamically based on the value of a certain
column. This appears to do what I want it to do but i'm no VB expert
and when trying to convert it across to my workbook, i get a runtime
error...

Run-time error '1004':
Method 'Range' of object '_Global' failed

When I click <<Debug the vb editor seta a break point at line 10

Code:
--------------------
Set rng = Range("Database")
--------------------


Am I missing something completely obvious???
(Code below)

Thanks in advance.


Code:
--------------------
Option Explicit

Sub ExtractLocations()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Cars without Photos")
Set rng = Range("Database")

'extract a list of Locations
ws1.Columns("B:B").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("B1").Value

For Each c In Range("J2:J" & r)
'add the Location to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A2"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A2"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function
--------------------


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #5   Report Post  
Morrigan
 
Posts: n/a
Default


I thought you wanted to collect all the data from the same row. (ie.
put out all the rows that contain "Location A" to sheet LocationA)
Maybe I misunderstood what you wanted to do.

Anyway, I am not VBA expert and would not even consider myself as a
beginner. :) Sorry cannot help you on VBA.


jarviscars Wrote:
Thanks Morrigan... but that seems to be returning any value of the same
row in the master sheet.

I found a sample workbook by Debra Dalgleish which used macros and
filters to create sheets dynamically based on the value of a certain
column. This appears to do what I want it to do but i'm no VB expert
and when trying to convert it across to my workbook, i get a runtime
error...

Run-time error '1004':
Method 'Range' of object '_Global' failed

When I click <<Debug the vb editor seta a break point at line 10

Code:
--------------------
Set rng = Range("Database")

--------------------


Am I missing something completely obvious???
(Code below)

Thanks in advance.


Code:
--------------------
Option Explicit


Sub ExtractLocations()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Cars without Photos")
Set rng = Range("Database")

'extract a list of Locations
ws1.Columns("B:B").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("B1").Value

For Each c In Range("J2:J" & r)
'add the Location to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A2"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A2"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function

--------------------



--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438



  #6   Report Post  
jarviscars
 
Posts: n/a
Default


I thought you wanted to collect all the data from the same row. (ie. put
out all the rows that contain "Location A" to sheet LocationA)


That's exactly what i wanted to do however if my recordset is as
follows:

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7

and I have a separate sheet for each location...

[SHEET 1] Location A
If I place the formula into every cell, it transfers all the data...
(including rows containing location B & C)


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #7   Report Post  
Morrigan
 
Posts: n/a
Default


I tested it at work and it only pulled all the rows that contained
"Location A". I will try again tomorrow.



jarviscars Wrote:
That's exactly what i wanted to do however if my recordset is as
follows:

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7

and I have a separate sheet for each location...

[SHEET 1] Location A
If I place the formula into every cell, it transfers all the data...
(including rows containing location B & C)



--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #8   Report Post  
Morrigan
 
Posts: n/a
Default


I tried again and didn't seem to be wrong to me. Since I am assuming
row1 to be your header, so there is ROW()-1 in my formula. If row1 is
where your data start, then replace "ROW()-1" with "ROW()".

ie.

A1 =
INDEX(Master!$A$1:$D$5,SUMPRODUCT(SMALL(ROW(Master !$1:$5)*(Master!$B$1:$B$5="Location
A"),COUNTA(Master!$B$1:$B$5)-COUNTIF(Master!$B$1:$B$5,"Location
A")+ROW())),COLUMN())

I've also attached my file(without header).


Hope it helps.


+-------------------------------------------------------------------+
|Filename: Choose.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3657 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #9   Report Post  
jarviscars
 
Posts: n/a
Default


Thanks morrigan,

I looked at your sample file and can follow the steps through... It
works fine with the sample but when I apply it to the data sheet i'm
working with... something gets 'lost in translation'

I've attached my data file for you to look at?

Cheers,


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3663 |
+-------------------------------------------------------------------+

--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #10   Report Post  
Morrigan
 
Posts: n/a
Default


Have a look at the attached file. Hope it will help.


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3665 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438



  #11   Report Post  
jarviscars
 
Posts: n/a
Default


Thanks for that..... Works great!

Could you please help me with an IF statement to return an empty cell
if the formula evaluates to false??? (at the moment the cell returns
#NUM if it does not evaluate to true.


Code:
--------------------
=INDEX('Cars without Photos'!$A$1:$H$500,SUMPRODUCT(SMALL(ROW('Cars without Photos'!$3:$500)*('Cars without Photos'!$B$3:$B$500=" Jarvis Gepps Cross"),COUNTA('Cars without Photos'!$B$3:$B$500)-COUNTIF('Cars without Photos'!$B$3:$B$500," Jarvis Gepps Cross")+ROW()+500-COUNTA('Cars without Photos'!$B$3:$B$500)-2-2)),COLUMN())
--------------------


Cheers,


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #12   Report Post  
Morrigan
 
Posts: n/a
Default


Maybe something like:

IF(ISERROR(...),"",(...))


jarviscars Wrote:
Thanks for that..... Works great!

Could you please help me with an IF statement to return an empty cell
if the formula evaluates to false??? (at the moment the cell returns
#NUM if it does not evaluate to true.


Code:
--------------------
=INDEX('Cars without Photos'!$A$1:$H$500,SUMPRODUCT(SMALL(ROW('Cars without Photos'!$3:$500)*('Cars without Photos'!$B$3:$B$500=" Jarvis Gepps Cross"),COUNTA('Cars without Photos'!$B$3:$B$500)-COUNTIF('Cars without Photos'!$B$3:$B$500," Jarvis Gepps Cross")+ROW()+500-COUNTA('Cars without Photos'!$B$3:$B$500)-2-2)),COLUMN())

--------------------


Cheers,



--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #13   Report Post  
jarviscars
 
Posts: n/a
Default


Fantastic.....

Muchly appreciated!


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #14   Report Post  
jarviscars
 
Posts: n/a
Default


Is it possible to substitute the string with wildcards???

I want to have a couple of sheets that search for stock # prefix...

here's the modified formula that i've come up with but it's not
returning the correct values...


Code:
--------------------
=IF(ISERROR(INDEX(Norwood!$A$1:$H$500,SUMPRODUCT(S MALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$500="D* "),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())),"",(INDEX(Norwood!$A$1:$H$500,SUMPR ODUCT(SMALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$ 500="D*"),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())))
--------------------


Essentially i've modified the formula to look on the sheet 'Norwood' in
'Column A' for anything with containing 'D*'

Unfortunately it doesn't return all the results. It appears to count
the number of rows containing the D prefix but then just returns the
top X number of rows as counted.

Thanks


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

  #15   Report Post  
Morrigan
 
Posts: n/a
Default


I am not sure how to use wildcards in a formula.

I edited the sheet "Norwood" and added a new sheet "Test". However,
formula gets fairly long after the IF(ISERROR(...),"",(...)) statement
in implemented. Personally I do not like it. It seems like you will
have quite a few sheets and each will have up to 500 rows of formula.
Time to complete calculation can be increased dramatically.

Good luck.



jarviscars Wrote:
Is it possible to substitute the string with wildcards???

I want to have a couple of sheets that search for stock # prefix...

here's the modified formula that i've come up with but it's not
returning the correct values...


Code:
--------------------
=IF(ISERROR(INDEX(Norwood!$A$1:$H$500,SUMPRODUCT(S MALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$500="D* "),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())),"",(INDEX(Norwood!$A$1:$H$500,SUMPR ODUCT(SMALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$ 500="D*"),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())))

--------------------


Essentially i've modified the formula to look on the sheet 'Norwood'
in 'Column A' for anything with containing 'D*'

Unfortunately it doesn't return all the results. It appears to count
the number of rows containing the D prefix but then just returns the
top X number of rows as counted.

Thanks



+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3672 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438



  #16   Report Post  
Krishnakumar
 
Posts: n/a
Default


Hi,

I think another option for you is make a database query. While making
the query you can filter the data.

Have a look at the attachment.

HTH


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3674 |
+-------------------------------------------------------------------+

--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatiing based on another cell Number_8 Excel Discussion (Misc queries) 3 March 13th 06 01:35 PM
Cell color based upon cell value My View Excel Discussion (Misc queries) 11 July 6th 05 03:59 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"