| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I assign a unique ID number to an invoice sheet I created? | Excel Worksheet Functions | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| How do you assign clip art to a cell? | Excel Worksheet Functions | |||
| Variable VB to assign new sheet name | Excel Worksheet Functions | |||
| Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Linear Mode
