Forum Discussion
Return a value from a list depending if value in another cell contains this
See the attached sample workbook. The Parts and Sizes names are dynamic in this version - they will automatically be adjusted when you add or remove part numbers. See Formulas > Name Manager for their definition.
See the attached sample workbook. The Parts and Sizes names are dynamic in this version - they will automatically be adjusted when you add or remove part numbers. See Formulas > Name Manager for their definition.
Hello,
I realize that this thread is older but this works really well for my purposes with a few issues.
Can you give a breakdown of the formula in Name Manager?
=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A)-1,1)
I'm using this on part numbers instead and have instances with similar part numbers returning the wrong data. I've outlined a few instances below.
1 BerryBites 62 CitrusZing 100 MangoMunch 5 PineapplePops 620 KiwiSqueeze 7 MelonMorsels So 100, is returning BerryBites instead of MangoMunch.
Can you lend some aid?
Thank you.
The formula says the following:
- Start in cell A1 of the Lists sheet.
- Go 1 cell down (because I assumed that A1 contains a header/column name), and 0 columns to the right, i.e. to A2.
- Expand to 1 row less than the number of filled rows in column A. The -1 is to omit the header in A1 from the count.
If you do not have a header in A1, the formula should be
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)
This looks like something i have been strugeling with ... However. Is it posible to the two sheets as two different files insted of inside the same worksheet?HansVogelaar
It's possible but I would not recommend it. The workbook with the lookup list would have to remain open, otherwise the formulas would return an error. That kind of defeats the purpose.
HansVogelaar Don't know if i can post this here or i need a new post enterly?
However the solution given in this threat is very close to what im looking for. Dont know if it is posible tho. To have an open sheet search for words for a not open file.
my problem is that i have many sheets that need to search for the same values and i dont want to update the database across all of the sheets. So if the database/list chould be located only one place then i chould updatse it just once.
excampels in photos:
- Thanks a lot! With your help I was finally able to built one on my sheet! I really appreciate your help. Have a great day.
