Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a look up forumula I haven't seen before
I'm trying to build an output formula for some information I need but haven't
seen anything like it. Here's what I'm doing: On the first tab I have a list of light fixtures. Cells A1..A# contains the quantiy of light fixture per type Cells B1..B# contains the "tag" name of the fixture (A, B, B1, EX, etc). Cells C1..C# contains the lamp style (eg... F032T8/835). Cells D1..D# contains the number of lamps per each fixture. On the 2nd tab sheet I have an output area that creates a list of the lamp types and the totals for each lamp type. In cell A1..A# I type the lamp styles I'm using on a particular job that match one of the lamps from the C1..C# range on the first tab-sheet. In cell B1..B# I'm using SUMIF to make a total for all the lamps used on the job from the first tab sheet that match my criteria as typed into cell A1..A# on this tab sheet. The next cell forumula is the one I need help with. In cell B1..B# I need a formula that will give me the "tag" names of the fixtures that meet the lamp criteria on this tab sheet that I typed in cell A1..A#. I would prefer it look like this: "Fixture Types ?, ?, ?, ?". If that's not possible, I could work with something more simple like just "?, ?, ?, ?" where "?" is the fixture "Tag" name from the first tab sheet for every fixture that uses the same lamp type. I hope this all makes sense. I tried to keep it simple but if someone thinks they have a solution for this one but needs more info I can email you the sheet I'm working with. Thank you. -- Steven Leuck Builders Electric, Inc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a look up forumula I haven't seen before
This can be done but you'd get the "tag's" in separate cells.
How many tag's can relate to any style? You can send a copy of the file to me and I'll see what I can do (no guarantees!). I use Excel 2002 so don't send an Excel 2007 format file! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Steven Leuck" wrote in message ... I'm trying to build an output formula for some information I need but haven't seen anything like it. Here's what I'm doing: On the first tab I have a list of light fixtures. Cells A1..A# contains the quantiy of light fixture per type Cells B1..B# contains the "tag" name of the fixture (A, B, B1, EX, etc). Cells C1..C# contains the lamp style (eg... F032T8/835). Cells D1..D# contains the number of lamps per each fixture. On the 2nd tab sheet I have an output area that creates a list of the lamp types and the totals for each lamp type. In cell A1..A# I type the lamp styles I'm using on a particular job that match one of the lamps from the C1..C# range on the first tab-sheet. In cell B1..B# I'm using SUMIF to make a total for all the lamps used on the job from the first tab sheet that match my criteria as typed into cell A1..A# on this tab sheet. The next cell forumula is the one I need help with. In cell B1..B# I need a formula that will give me the "tag" names of the fixtures that meet the lamp criteria on this tab sheet that I typed in cell A1..A#. I would prefer it look like this: "Fixture Types ?, ?, ?, ?". If that's not possible, I could work with something more simple like just "?, ?, ?, ?" where "?" is the fixture "Tag" name from the first tab sheet for every fixture that uses the same lamp type. I hope this all makes sense. I tried to keep it simple but if someone thinks they have a solution for this one but needs more info I can email you the sheet I'm working with. Thank you. -- Steven Leuck Builders Electric, Inc. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a look up forumula I haven't seen before
Biff,
THANK YOU!! That was perfect. It was just what I needed to finish up this form and completely automate a dedious task. I took what you gave me and expanded it slightly for those times when I may have more than I showed in the example. I tested it out to make sure it still worked as you intended and found that it di. Thanks again for taking the time to help an amatuer. -- Steven Leuck Builders Electric, Inc. "T. Valko" wrote: This can be done but you'd get the "tag's" in separate cells. How many tag's can relate to any style? You can send a copy of the file to me and I'll see what I can do (no guarantees!). I use Excel 2002 so don't send an Excel 2007 format file! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Steven Leuck" wrote in message ... I'm trying to build an output formula for some information I need but haven't seen anything like it. Here's what I'm doing: On the first tab I have a list of light fixtures. Cells A1..A# contains the quantiy of light fixture per type Cells B1..B# contains the "tag" name of the fixture (A, B, B1, EX, etc). Cells C1..C# contains the lamp style (eg... F032T8/835). Cells D1..D# contains the number of lamps per each fixture. On the 2nd tab sheet I have an output area that creates a list of the lamp types and the totals for each lamp type. In cell A1..A# I type the lamp styles I'm using on a particular job that match one of the lamps from the C1..C# range on the first tab-sheet. In cell B1..B# I'm using SUMIF to make a total for all the lamps used on the job from the first tab sheet that match my criteria as typed into cell A1..A# on this tab sheet. The next cell forumula is the one I need help with. In cell B1..B# I need a formula that will give me the "tag" names of the fixtures that meet the lamp criteria on this tab sheet that I typed in cell A1..A#. I would prefer it look like this: "Fixture Types ?, ?, ?, ?". If that's not possible, I could work with something more simple like just "?, ?, ?, ?" where "?" is the fixture "Tag" name from the first tab sheet for every fixture that uses the same lamp type. I hope this all makes sense. I tried to keep it simple but if someone thinks they have a solution for this one but needs more info I can email you the sheet I'm working with. Thank you. -- Steven Leuck Builders Electric, Inc. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a look up forumula I haven't seen before
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steven Leuck" wrote in message ... Biff, THANK YOU!! That was perfect. It was just what I needed to finish up this form and completely automate a dedious task. I took what you gave me and expanded it slightly for those times when I may have more than I showed in the example. I tested it out to make sure it still worked as you intended and found that it di. Thanks again for taking the time to help an amatuer. -- Steven Leuck Builders Electric, Inc. "T. Valko" wrote: This can be done but you'd get the "tag's" in separate cells. How many tag's can relate to any style? You can send a copy of the file to me and I'll see what I can do (no guarantees!). I use Excel 2002 so don't send an Excel 2007 format file! I'm at: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Steven Leuck" wrote in message ... I'm trying to build an output formula for some information I need but haven't seen anything like it. Here's what I'm doing: On the first tab I have a list of light fixtures. Cells A1..A# contains the quantiy of light fixture per type Cells B1..B# contains the "tag" name of the fixture (A, B, B1, EX, etc). Cells C1..C# contains the lamp style (eg... F032T8/835). Cells D1..D# contains the number of lamps per each fixture. On the 2nd tab sheet I have an output area that creates a list of the lamp types and the totals for each lamp type. In cell A1..A# I type the lamp styles I'm using on a particular job that match one of the lamps from the C1..C# range on the first tab-sheet. In cell B1..B# I'm using SUMIF to make a total for all the lamps used on the job from the first tab sheet that match my criteria as typed into cell A1..A# on this tab sheet. The next cell forumula is the one I need help with. In cell B1..B# I need a formula that will give me the "tag" names of the fixtures that meet the lamp criteria on this tab sheet that I typed in cell A1..A#. I would prefer it look like this: "Fixture Types ?, ?, ?, ?". If that's not possible, I could work with something more simple like just "?, ?, ?, ?" where "?" is the fixture "Tag" name from the first tab sheet for every fixture that uses the same lamp type. I hope this all makes sense. I tried to keep it simple but if someone thinks they have a solution for this one but needs more info I can email you the sheet I'm working with. Thank you. -- Steven Leuck Builders Electric, Inc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If forumula for 4 conditions | Excel Discussion (Misc queries) | |||
If more than condition forumula | Excel Worksheet Functions | |||
New Forumula Issue | Excel Discussion (Misc queries) | |||
Forumula help using Countif | Excel Worksheet Functions | |||
Help with FORUMULA | Excel Discussion (Misc queries) |