Hi guys,




I had an idea for a document: there is a worksheet with a table, tons of entries (rows) in this table. However in a second worksheet, certain characteristics are looked up from that big table, and only certain rows are returned.


The document has to do this "live" (real time) without needing to manually press buttons, and the document cannot use macros, plus it has to be compatible with 2007 excel version. So really it must use smart code inside some cells.


The idea is, that anybody could add items onto the end of the big messy table, however in another worksheet, relevant items would continue to be shown there, even when new ones are added.



What I don't have a problem with:
Returning certain rows based on info from a column
Removing duplicate entires (haven't run into this yet)...


What I do have a problem with:
Removing any and all spaces from the "smaller" list that is generated.



As someone with rudimentary coding knowledge, I set out to find a method where this can be done, but man, was it not easy - I learned about iterative/circular functions, where cells can re-calculate and have some self-awareness regarding their own contents, and this worked great for me, I made some test programs and they worked, however they weren't "manipulating cells" they were just maths...



So, I am treating this a bit like stack overflow, I have provided a minimum reproducible example. In the attached document there are two little tables of data, I removed anything that was not necessary, so it's very bare bones. I don't even think these functions support the addition of new data when they are "live" yet, but I am dealing with one problem at a time.


The left column, is the "original data", so to say, so it is not to be touched, and the right column is supposed to be the version with spaces removed.


If you write anything in cell I3 (I for indigo), the cells will begin work, but they will only iterate once - if you press F9 a bunch of times, they will get to the end of what they're doing (move everything to top, without affecting order). My question is, can I do anything to make it automatic, so that I don't need to press F9 a bunch of times? By the way, deleting whatever is inside cell I3 will "reset" everything and stop functions from calculating stuff, it's a little debugging thing.



I decided to write here because maybe there is a better way, obviously the end result of what I want to do will be quite long code, but I don't want people to do all my work for me, I am just stuck on one specific part of things.


Thanks in advance, love you all

What the heck, I attached and uploaded the doc, where did it go??


Sorry guys, here is a Google Drive link to the doc:


Книга5.xlsx - Google Sheets


Incredibly, Google tries to open the file, just do "file>download" at the top, please don't view it in Google sheets, I doubt anything good will come from that.

Hi
please post a sheet on the board ( external links are potentially unsafe), use the " go advanced" button in the Quick Reply window

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi, thanks for the reply. I swear I've been around the block, but it just didn't attach my dang doc, I will try again!

Please work...

Attached Files

Книга5.xlsx (10.3 KB, 2 views)

Have a look at sheet Лист2, column C.
I don't have Excel 2007, so the formula in cell C3 may have to be entered using Ctrl+Shift+Enter rather than just Enter, then copied down.
For real data, the ranges may have to be extended.
Note also that the ROW()-2 part of the formula contains a 2 because the first cell is in row 3; row 3 - 2 returns 1, the SMALLest index to return. This means, whichever row your topmost formula is in, you need to replace the 2 with the row number of the formula - 1.
Not sure if IFERROR is available in Excel 2007 so the same formula without it in column B.


Anyway, if should give you some ideas.

Attached Files

msofficeforums51512Книга5.xlsx (11.9 KB, 4 views)