Villeroy, thanks a lot for your database solution, I really like it! I currently have everything in Calc so I’d prefer to keep using it, because I’m not yet ready to switch to learning & using the database.
90% of today’s spreadsheet users desperately try to turn a spreadsheet into a database. Epic fail on the large scale.
1 Like
Are those integers 9,10,15,23,… ID numbers representing some items of another table?
I agree, that spreadsheets are not a database, and I will eventually switch over to using a database.
.
No, those integers are stand-alone values and do not represent any other cells or table.
Suggestion needs extra Column to work.
example file…
copy new rows if match_GS.ods (24,0,KB)
example file 2… reviewed
Lot Identify copy_en_118477a (1).ods (38,3,KB)
Schiavinatto, I want to thank you for this example. I’m glad I came across this. I have been looking for a way to only list Lots that have not been sold along with its price. Your example seems to be the answer. I put together a calc sheet that combines what will be two sheets to make the example easier to look at. The issue I come across is that although the hidden column works perfectly(THANK YOU!) when it comes to the lots and prices somehow it ignores the first 7 on most that have more than a few entries and some are ignored completely if only one or two lots are identified. Am I not seeing something here? the formulas are basically exactly what you provided in your example. I would really appreciate if you can take a look at it and see if you can see what I can not. running LIBRE 25.2 on Mac with current OS.
Lot Identify copy.ods (40.0 KB)
Hi, @Sphex1411 , correct formulas
From:
=SE(SEERRO(MENOR(B$8:B$411;LINHA()-1);"")="";"";ÍNDICE(J$9:J$411;CORRESP(1;(B$9:B$411=MENOR(B$8:B$411;LINHA()-1));0)))
To:
=SE(SEERRO(MENOR(B$9:B$411;LINHA()-1);"")="";"";ÍNDICE(J$9:J$411;CORRESP(1;(B$9:B$411=MENOR(B$9:B$411;LINHA()-8));0)))
From:
=SE(E9="";"";ÍNDICE(L$9:L$411;CORRESP(1;(B$9:B$411=SEERRO(MENOR(B$8:B$411;LINHA()-1);""));0)))
To:
=SE(E9="";"";ÍNDICE(L$9:L$411;CORRESP(1;(B$9:B$411=SEERRO(MENOR(B$9:B$411;LINHA()-8);""));0)))
Lot Identify copy_en_118477.ods (41,8,KB)
My Man, you are the man. thank you very much . I see now, I used your offset instead as to where I began. I so appreciate your time! This allows me to implement something I have been wondering how I would do it. I also learned a few things. Thank you again!!!
Well sir although my previous post is still true, as I change the symbol “NOK” to another, it does not work as it does with NOK I get missing lots or no lots at all. If you change NOK to QBTS the result is not complete. But I appreciate what you did and feel the solution is close.
Thanks for the quick response. I appreciate the drop down list however it does not solve the issue of listing all the not sold lots that exists for the symbol selected. The only one that seems to work is the original symbol NOK. I must be missing something or maybe my preference settings are different somewhere than what you have if it works for you. Example if you select INTC the result should show 500 for lot and 26.34 as price but it does not. QBTS should have 13 rows but only has 6. Thanks again
Hi, @Sphex1411 , correct formulas, sorry, I flattened this correction
=SE(SEERRO(MENOR(B$8:B$411;LINHA()-1);"")="";"";ÍNDICE(J$9:J$411;CORRESP(1;(B$9:B$411=MENOR(B$8:B$411;LINHA()-8));0)))
From …LINHA()-1
To: …LINHA()-8
1 Like
BINGO that you!!! Rest your eyes … I know I could not see it… Bless you.
1 Like