lookup

[Andy B1963]

Hi there guys

I am trying to build a spreadsheet that can return data from a master spreadsheet

the master sheet consists of a number of columns
a. vehicle fleet numbers
b vehicle types
c registration
d availability
e location

i'm trying to find a formula that can on the second report sheet tell me what vehicle / fleet numbers are available at each of the locations

thought i had the formula but seam to have lost it

can anyone help please

 

[AhoyNC]

Maybe something like this?
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Drag formula down and across as needed.

Excel Workbook
	A	B	C	D	E
1	vehicle fleet numbers	vehicle types	registration	availability	location
2	100-452			y	L1
3	100-453			y	L2
4	100-454			n	L3
5	100-455			y	L1
6	100-456			n	L2
7	100-457			n	L3
8	100-458			y	L1
9	100-459			y	L2
10	100-460			y	L3
11
12
13	Location
14	L1	L2	L3
15	100-452	100-453	100-460
16	100-455	100-459
17	100-458
18
Sheet

 

[Andy B1963]

Thanks AhoyNC

still struggling
wanting to put report onto a second spreadsheet also forgot to say wanted the report to separate the vehicle types

a1 location
a2 vehicle type (Van)
b2 vehicle type (Car)
c1 location
c2 vehicle type (Van)
d2 Vehicle type (Car)
e1 location
e2 vehicle type (Van)
f2 Vehicle type (Car)

unsure if this is possible

cheers

 

[AhoyNC]

It would help if you post a small sample of your sheet 1 & 2 and what the result should be.

Some questions:
1-What ver. of Excel are you using?
2-Can a vehicle be listed more than once in sheet 1 as both available and not? If so how do you known if available or not?
3-Can a vehicle be listed at different locations at different times? If so, what tells you which location is current?
4-What does the actual data look like under each header?
5-Based on your post#3 would A3 start the list of van numbers for location 1 and B3 would start list of car numbers for location 1?

 

[Andy B1963]

It would help if you post a small sample of your sheet 1 & 2 and what the result should be.

Some questions:
1-What ver. of Excel are you using?
2-Can a vehicle be listed more than once in sheet 1 as both available and not? If so how do you known if available or not?
3-Can a vehicle be listed at different locations at different times? If so, what tells you which location is current?
4-What does the actual data look like under each header?
5-Based on your post#3 would A3 start the list of van numbers for location 1 and B3 would start list of car numbers for location 1?

Hi there

attached

this is a section of the main spreadsheet

as said before looking to have a summary report that tells me what vehicle is at each location as the vehicles are relocated to different places daily or moved to workshops as (VOR)

hope this is of help

 

[AhoyNC]

There is no attachment to your post. The below example may help you set up your sheet2. The formulas I have below only work if the vehicle number is only listed once in the data sheet (sheet1). If a vehicle is listed multiple times the example below will only find the first listing.

One way the usually works to post sample data is to put a boarder around the sample and then use copy paste.
All the formulas are array formulas that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	L1		L2		L3
2	Vehicle Van	Vechile Car	Vehicle Van	Vechile Car	Vehicle Van	Vechile Car
3	100-452	100-458	100-459	100-453		100-457
4	100-455					100-460
Sheet2

Excel Workbook
	A	B	C	D	E
1	vehicle fleet numbers	vehicle types	registration	availability	location
2	100-452	Van		y	L1
3	100-453	Car		y	L2
4	100-454	Car		n	L3
5	100-455	Van		y	L1
6	100-456	Van		n	L2
7	100-457	Car		y	L3
8	100-458	Car		y	L1
9	100-459	Van		y	L2
10	100-460	Car		y	L3
Sheet1