dynamic filter/vlookup issue for multitabbed spreadsheet

StumpedBySpreadsheet · February 27, 2025, 10:06am

this is a sample of what i’m trying to accomplish. sadly, it appears to work differently than MSOffice and GoogleSheets and i just cannot figure out how to get it to modify [import/filter/update] the 2nd, 3rd, and 4th column every time i update the base data on tab 1.
i’ve put a basic note on the extract tabs saying that i want imported.

i’ve watched several videos, read a few articles, and tried modifying commands from older [MSOffice and GoogleSheet] spreadsheets and have not been able to figure it out. sadly all of them handle commands much more basic than what i want.

can someone please provide the command strings i need? i would post the original, but it has proprietary/copyrighted data, so i cannot share it. i will be perfectly happy if it is Filter or Vlookup, i just need it to update “constantly” since the data changes multiple times/day for tab 1.

sorry, i forgot to mention, i want it to pull data form the entire row each time the factor[s] i’m looking for is present.

thanks!

EDIT: here is an example of a filter i’ve used for a dynamic spreadsheet for my Steam game library
=FILTER(‘All Games’!A2:D,‘All Games’!D2:D=“Verified”) [GoogleSheet]

EDIT2: in case it matters, my actual spreadsheet is 23 tabs, where the raw data is tab1, for every item present (value >0) is on tab 2, and i want the other 21 tabs to pull data from tab 2. sadly, it has been over 2 years since i frequently had to use vlookup or filters

fpy · February 27, 2025, 3:25pm

#more-details

StumpedBySpreadsheet · February 28, 2025, 3:07am

Winn11 [pc], Linux Mint [laptop, sorry don’t recall ver, but literally fully updated within the past 72 hours], Pop!_OS [laptop, have not updated in more than 4 days, can’t recall current version], LibreOffice 24.8.5.2 (x86_64), build fddf2685c70b461e7832239a0162a77216259f22

karolus · February 27, 2025, 3:27pm

import pandas as pd
from operator import eq, and_, ge, gt, le, lt

def filter_data_to_sheets():
    doc = XSCRIPTCONTEXT.getDocument()
    core = doc.Sheets['core data']
    cursor =core.createCursorByRange(core['A1'])
    cursor.gotoEndOfUsedArea(True)    
    header, *data = cursor.DataArray
    frame = pd.DataFrame(zip(*data), index=header).T
    
    filters = [ 
                gt( frame.qauntity, 0), 
                eq( frame.trait1, 'white'),
                and_( eq( frame.trait2, 'fast') , eq( frame.trait3, 'past'))
              ]
                
    for n, _filter in enumerate(filters, 1):
        filtered = frame[ _filter ].values
        out = [header]
        out.extend(tuple(row) for row in filtered)
        doc.Sheets[n].clearContents(15)
        doc.Sheets[n][:len(out), :len(out[0])].DataArray = out

StumpedBySpreadsheet · February 28, 2025, 3:00am

thank you. sadly, i don’t know how to use that info, but i’ll be researching it as soon as i have free time again. as in i don’t know how to write that formula into LibreOffice [sorry, i’m much more familiar with MSOffice and the small differences still throw me off]