I have many .ods spreadsheets with data. On the spreadsheets the data fields are on row 1, each field has its own column, I have 93 fields, so 93 columns for data. The data starts on row 2 with, the data in each field being in the appropriate column, there are many rows of data.
I only need the data from 7 of the 93 fields. What I do currently do to view the data I need is I create a second sheet, on row 1 of the second sheet I fill in the field names that I want in columns A thru G, then starting on row 2 I put in simple formulas like this into the columns:
=$Sheet1.C2, =$Sheet1.G2, =$Sheet1.L2, =$Sheet1.U2, =$Sheet1.Y2, =$Sheet1.AF2, =$Sheet1.AO2
I then paste the formula down the columns. This lets me see only the data of the fields I want. To try and simplify this, I saved this spreadsheet with my 2nd sheet formulas as a blank template where I would paste all the data into sheet1 and sheet2 would act as a filter to show me the data. The only problem is the data speadsheets that I have do not have the same order of the data fields names, they do not display in the same columns, they are in rather random columns for each spreadsheet, but the fields names are the same and are found in all my spreadsheets they are just not in the same location, which leads me to track down the column of the field I want on sheet1 and edit the formulas in sheet2 to display the appropriate data. I tried a pivot table but it displayed my data in alphebetical order, the order of the fields in my sheet2 have to be the specific order I have in the row 1 data fields.
Looking for recommendations to simplify my process, any help is appreciated. I’m on windows 10, LO version 6.4.4.2 x64.