Lookup numbers in sheet and assign it's cell reference
 

Hello,

I am after some help again, I have a worksheet(Shed) and the range
where numbers will be entered are B3:E46. In another sheet(Location) I
have a in column A "Shift #", B "Shift Bus #" C "Location". what I am
attempting to do is when a bus number is entered into Shed! ie..
b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and
match it to Location! (column B) and from there provide the cell ref in
column C. So I could then print Location! that would give me in Shift #
order where each Bus # the shift is assigned to.


Thanks for any assistance


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Hi!

Glad we got that data validation deal worked out!

Ok.........

In sheet "Location" assume the bus numbers are listed starting in B2. Enter
this formula in Location C2 as an array using the key combo of
CTRL,SHIFT,ENTER:

=ADDRESS(MAX((Shed!B$3:E$46=B2)*(ROW(Shed!B$3:E$46 ))),MAX((Shed!B$3:E$46=B2)*(COLUMN(Shed!B$3:E$46)) ))

Copy down as needed.

Biff

"dgraham" wrote in
message ...

Hello,

I am after some help again, I have a worksheet(Shed) and the range
where numbers will be entered are B3:E46. In another sheet(Location) I
have a in column A "Shift #", B "Shift Bus #" C "Location". what I am
attempting to do is when a bus number is entered into Shed! ie..
b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and
match it to Location! (column B) and from there provide the cell ref in
column C. So I could then print Location! that would give me in Shift #
order where each Bus # the shift is assigned to.


Thanks for any assistance


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile:
http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Let's tweak that a little..........

You probably don't need to have the address returned as absolute so we'll
get rid of all those $ signs, and, let's add a bit to make sure there's a
bus number entered in column B:

Still array entered:

=IF(B2="","",ADDRESS(MAX((Shed!B$3:E$46=B2)*(ROW(S hed!B$3:E$46))),MAX((Shed!B$3:E$46=B2)*(COLUMN(She d!B$3:E$46))),4))

Biff

"Biff" wrote in message
...
Hi!

Glad we got that data validation deal worked out!

Ok.........

In sheet "Location" assume the bus numbers are listed starting in B2.
Enter this formula in Location C2 as an array using the key combo of
CTRL,SHIFT,ENTER:

=ADDRESS(MAX((Shed!B$3:E$46=B2)*(ROW(Shed!B$3:E$46 ))),MAX((Shed!B$3:E$46=B2)*(COLUMN(Shed!B$3:E$46)) ))

Copy down as needed.

Biff

"dgraham" wrote in
message ...

Hello,

I am after some help again, I have a worksheet(Shed) and the range
where numbers will be entered are B3:E46. In another sheet(Location) I
have a in column A "Shift #", B "Shift Bus #" C "Location". what I am
attempting to do is when a bus number is entered into Shed! ie..
b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and
match it to Location! (column B) and from there provide the cell ref in
column C. So I could then print Location! that would give me in Shift #
order where each Bus # the shift is assigned to.


Thanks for any assistance


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile:
http://www.excelforum.com/member.php...o&userid=33138
View this thread:
http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Thanks again Biff,

That works, now is there a way of getting the the shed location for the
cell reference ie.. shed!B3 refers to 1D in the shed and shed!C3 refers
to 1C in the shed. there are 4 spots in each row ranging from 1A to
44D. for example

bus 900 is entered at $C$28 and this cell refers to the shed number
26C. and the 26C is what I would like printed in Location! Column C. so
when I have finished entering all the buses, the shed address ie.. 26C
will correspond to the Shift bus# in Location! column B. Confusing!


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Argh!

LOL!

Confusing!

Very!

Can I see your file?

Biff

"dgraham" wrote in
message ...

Thanks again Biff,

That works, now is there a way of getting the the shed location for the
cell reference ie.. shed!B3 refers to 1D in the shed and shed!C3 refers
to 1C in the shed. there are 4 spots in each row ranging from 1A to
44D. for example

bus 900 is entered at $C$28 and this cell refers to the shed number
26C. and the 26C is what I would like printed in Location! Column C. so
when I have finished entering all the buses, the shed address ie.. 26C
will correspond to the Shift bus# in Location! column B. Confusing!


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile:
http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

I've attached the file, hope you can see what i'm trying to do.

Regards

David


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

--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Ok........

Based on the sample you posted:

Enter this formula as an array:

=IF(B2="","",MAX((Shed=B2)*(ROW($1:$44)))&CHOOSE(M AX((Shed=B2)*(COLUMN(Shed))),"","B","C","D","E"))

Copy down as needed.

Notice that I'm using your named range, Shed.

ROW($1:$44) refers to the numbers listed in Shed!F3:F46.

Biff

"dgraham" wrote in
message ...

I've attached the file, hope you can see what i'm trying to do.

Regards

David


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

--
dgraham
------------------------------------------------------------------------
dgraham's Profile:
http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Thanks Biff,

we're almost there, the correct row number is shown, but not the
position number.

If you look at bus 903 in shed!(E28), even though it is entered into
column E it is in position A, as it is in the front of row 26. so 903
should show 26A, 902 should be 26B, and 901 should be 26C.

any ideas?


Regards


David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

Ok, I see now. You want the relative address as it relates to the table,
right to left.

Just change this portion of the formula:

..........,"","B","C","D","E"))

Change to:

...........,"","D","C","B","A"))

Biff

"dgraham" wrote in
message ...

Thanks Biff,

we're almost there, the correct row number is shown, but not the
position number.

If you look at bus 903 in shed!(E28), even though it is entered into
column E it is in position A, as it is in the front of row 26. so 903
should show 26A, 902 should be 26B, and 901 should be 26C.

any ideas?


Regards


David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile:
http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

hi Biff,


Thanks so much, now it works the way it should. I really appreciate
your help getting this worksheet to work. not sure how it works, I will
do a little study on the formula. Thanks again.


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335

Lookup numbers in sheet and assign it's cell reference
 

You're welcome. Thanks for the feedback!

Biff

"dgraham" wrote in
message ...

hi Biff,


Thanks so much, now it works the way it should. I really appreciate
your help getting this worksheet to work. not sure how it works, I will
do a little study on the formula. Thanks again.


Regards

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile:
http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=530335