Return cell contents based on conditional lookup

[jarviscars]

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.

Therefore:

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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Morrigan]

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.

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.

Therefore:

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]

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

Please Login or Register  to view this content.

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

Thanks in advance.

Please Login or Register  to view this content.

[Morrigan]

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.

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

Please Login or Register  to view this content.

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

Thanks in advance.

Please Login or Register  to view this content.

[jarviscars]

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)

[Morrigan]

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

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]

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.

[jarviscars]

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,

[Morrigan]

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

[jarviscars]

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.

Please Login or Register  to view this content.

Cheers,

[Morrigan]

Maybe something like:

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

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.

Please Login or Register  to view this content.

Cheers,

[jarviscars]

Fantastic.....

Muchly appreciated!

[jarviscars]

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

Please Login or Register  to view this content.

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

[Morrigan]

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. I heard there is a way not to display any error, that might help.

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.

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

Please Login or Register  to view this content.

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

[Krishnakumar]

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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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

[Derek Y via OfficeKB.com]

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
>
>Therefore:
>
>*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