Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Hi
I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
For one way, see this screencap:
http://img530.imageshack.us/img530/2452/sumifer2.jpg -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
look in the help for vlookup, that's the formula that does exactly what you
want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Hi
I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Thanks a lot but I tried that SUMIF Function on the basis of what you showed,
but I only get #NAME? Nice try, but I still have this problem.!!! Sorry to be a pain, but I did check the Syntax Very carefully and no joy I'm afraid. "T. Valko" wrote: For one way, see this screencap: http://img530.imageshack.us/img530/2452/sumifer2.jpg -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
You can upload small files here for free:
http://tinyurl.com/24xfnt This is a French site translated into English. The files will remain active for about 3 weeks. After you upload the file you'll get a link to that file then you can post the link for anyone wanting to look at the file. I post sample files like this quite often. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Are you using a English language version of Excel?
-- Regards, Peo Sjoblom "Robert" wrote in message ... Thanks a lot but I tried that SUMIF Function on the basis of what you showed, but I only get #NAME? Nice try, but I still have this problem.!!! Sorry to be a pain, but I did check the Syntax Very carefully and no joy I'm afraid. "T. Valko" wrote: For one way, see this screencap: http://img530.imageshack.us/img530/2452/sumifer2.jpg -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
No
I'm using Star Office for this. Would that be any different. I'll try it in excel as well and let you know. Thanks "Peo Sjoblom" wrote: Are you using a English language version of Excel? -- Regards, Peo Sjoblom "Robert" wrote in message ... Thanks a lot but I tried that SUMIF Function on the basis of what you showed, but I only get #NAME? Nice try, but I still have this problem.!!! Sorry to be a pain, but I did check the Syntax Very carefully and no joy I'm afraid. "T. Valko" wrote: For one way, see this screencap: http://img530.imageshack.us/img530/2452/sumifer2.jpg -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Thanks.
I've done that and the file is he http://cjoint.com/?kFuFfycjWl Robert "T. Valko" wrote: You can upload small files here for free: http://tinyurl.com/24xfnt This is a French site translated into English. The files will remain active for about 3 weeks. After you upload the file you'll get a link to that file then you can post the link for anyone wanting to look at the file. I post sample files like this quite often. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Here's a screencap of the file you posted with the formulas entered. As you
can see, the formula I suggested does return the correct results. I made a slight tweak to the formula to account for empty cells like B3. http://img219.imageshack.us/img219/1644/sumif2ok8.jpg I entered the formula in cell C3 then drag copied down to cell C5. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks. I've done that and the file is he http://cjoint.com/?kFuFfycjWl Robert "T. Valko" wrote: You can upload small files here for free: http://tinyurl.com/24xfnt This is a French site translated into English. The files will remain active for about 3 weeks. After you upload the file you'll get a link to that file then you can post the link for anyone wanting to look at the file. I post sample files like this quite often. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Ok...here is an actual file I'm working with now, given your forumula, which
did work, so thanks for that. However now in the file that is attached at: http://cjoint.com/?lbqLmGZyyh Why am I getting Zero Returned in the Sheet Called Beko at the Cell Ref: G2 and the rest of that column. Thanks. Robert "T. Valko" wrote: Here's a screencap of the file you posted with the formulas entered. As you can see, the formula I suggested does return the correct results. I made a slight tweak to the formula to account for empty cells like B3. http://img219.imageshack.us/img219/1644/sumif2ok8.jpg I entered the formula in cell C3 then drag copied down to cell C5. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks. I've done that and the file is he http://cjoint.com/?kFuFfycjWl Robert "T. Valko" wrote: You can upload small files here for free: http://tinyurl.com/24xfnt This is a French site translated into English. The files will remain active for about 3 weeks. After you upload the file you'll get a link to that file then you can post the link for anyone wanting to look at the file. I post sample files like this quite often. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Thanks again, as I've now realised I had one wrong Cell Reference. What a
Bad Boy I am. Seriously, this has helped me a lot and I want to thank you for your help. May I ask one further question on this though..please..!! I've copied up the revised version with that Formula Fixed. ITs He http://cjoint.com/?lbsOyZZRYw My Question is again relating to the Worksheet contained in the file, which is Entitled Beko. At the bottom I've put in a calculation for The Total Units and Tonnage, but I want to know whether its possible to get it to change the result of the calculation, whenever the Filtering is altered. For Example I may change the Date to Filter on, but I would then want to see the Totals Change also. At the moment, it doesn't alter it but actually hides it, and even if I have hidden it through filtering, its still doesn't JUST calcuate on what I've filtered. Hope that makes sense. Many Many thanks Robert "Robert" wrote: Ok...here is an actual file I'm working with now, given your forumula, which did work, so thanks for that. However now in the file that is attached at: http://cjoint.com/?lbqLmGZyyh Why am I getting Zero Returned in the Sheet Called Beko at the Cell Ref: G2 and the rest of that column. Thanks. Robert "T. Valko" wrote: Here's a screencap of the file you posted with the formulas entered. As you can see, the formula I suggested does return the correct results. I made a slight tweak to the formula to account for empty cells like B3. http://img219.imageshack.us/img219/1644/sumif2ok8.jpg I entered the formula in cell C3 then drag copied down to cell C5. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks. I've done that and the file is he http://cjoint.com/?kFuFfycjWl Robert "T. Valko" wrote: You can upload small files here for free: http://tinyurl.com/24xfnt This is a French site translated into English. The files will remain active for about 3 weeks. After you upload the file you'll get a link to that file then you can post the link for anyone wanting to look at the file. I post sample files like this quite often. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value from a Table, dependant on the word Typed
Insert 2 or 3 rows at the very top of the sheet and put the formulas *above*
the filter. That way they're *always* visible. Use the SUBTOTAL function to do calculations on filtered data. See Excel help on the SUBTOTAL function to see all the calculations that it can do. Here's the formulas you should use. I inserted 2 new rows at the top so the filtered data starts on row 4. =SUBTOTAL(9,B4:B218) =SUBTOTAL(9,G4:G218) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks again, as I've now realised I had one wrong Cell Reference. What a Bad Boy I am. Seriously, this has helped me a lot and I want to thank you for your help. May I ask one further question on this though..please..!! I've copied up the revised version with that Formula Fixed. ITs He http://cjoint.com/?lbsOyZZRYw My Question is again relating to the Worksheet contained in the file, which is Entitled Beko. At the bottom I've put in a calculation for The Total Units and Tonnage, but I want to know whether its possible to get it to change the result of the calculation, whenever the Filtering is altered. For Example I may change the Date to Filter on, but I would then want to see the Totals Change also. At the moment, it doesn't alter it but actually hides it, and even if I have hidden it through filtering, its still doesn't JUST calcuate on what I've filtered. Hope that makes sense. Many Many thanks Robert "Robert" wrote: Ok...here is an actual file I'm working with now, given your forumula, which did work, so thanks for that. However now in the file that is attached at: http://cjoint.com/?lbqLmGZyyh Why am I getting Zero Returned in the Sheet Called Beko at the Cell Ref: G2 and the rest of that column. Thanks. Robert "T. Valko" wrote: Here's a screencap of the file you posted with the formulas entered. As you can see, the formula I suggested does return the correct results. I made a slight tweak to the formula to account for empty cells like B3. http://img219.imageshack.us/img219/1644/sumif2ok8.jpg I entered the formula in cell C3 then drag copied down to cell C5. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks. I've done that and the file is he http://cjoint.com/?kFuFfycjWl Robert "T. Valko" wrote: You can upload small files here for free: http://tinyurl.com/24xfnt This is a French site translated into English. The files will remain active for about 3 weeks. After you upload the file you'll get a link to that file then you can post the link for anyone wanting to look at the file. I post sample files like this quite often. -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hi I did think that VLOOKUP would be the answer, but trying as I have done, I always get #VALUE. I made a small example of it, and if I could send it anywhere, you would see where I'm going wrong. Is that possible. Robert "Carlo" wrote: look in the help for vlookup, that's the formula that does exactly what you want. If you need help in applying this formula, just ask. hth Carlo "Robert" wrote: Hi I'm trying return the weight of an item into an adjacent cell, when I type the word. To Explain: If I have A1 and B1 as Item and Weight, then underneath those I have Sofa, Chair, Bed etc, with the relevant weights in the B Column. Next I have another Worksheet or the Same Worksheet, where I have Name, Item and Weight. I want to insert automatically the Weight based on typing the right Item (i.e. Sofa) in the cell next to the persons name. Hope that makes sense. Can't seem to upload a sample. I may have about 100 items to access along with their weights. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fitting cell to typed word | Excel Discussion (Misc queries) | |||
Why does the word typed "true" automatically turn to ALL CAPS? | New Users to Excel | |||
Returning a Value dependant on a cell which could have different d | Excel Worksheet Functions | |||
when word is typed in cell, how can the row auto displays data? | Excel Discussion (Misc queries) | |||
copy a number typed in a cell to another & change it to word form. | Excel Discussion (Misc queries) |