How to search for information from another worksheet in Excel

Question

How to search for information from another worksheet in Excel

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have a system that generates .xls spreadsheets daily, for example "array_070120150900.xls". Where the numbers represent the day and time 07/01/2015 at 09:00. I've created a worksheet for managing this data called gestion.xls. How do I manage.xls identify the array_xxxxxxxxxxxx.xls file so that I can perform operations of the array_xxxxxxxxxxxx.xls data in the gestion.xls worksheet?

Note: array_070120150900.xls varies by day. Today is array_070120150900.xls, tomorrow will be another array_080120151000.xls spreadsheet

I can fetch from a spreadsheet with the fixed name:

Exemplo:

matriz_070120150900.xls
A1 - Salário
B1 - R$100.000

gerencia.xls
A1 - Diretor
B1 - =[matriz_070120150900.xls]Salário!$B$1

excel

asked by anonymous 07.01.2015 / 13:15

2 answers

Calling dictionary in another View Controller Swift ___ ___ erkimt How to minimize the reduction of compatibiliade due to the use of permissions? ______ qstntxt ___

In my new version when putting these permissions in Manifest, I had a reduction of compatibility with several devices, would I have some way to verify this or who does not support these permissions will be left out anyway? %pre%

______ azszpr45473 ___

One of the reasons for this problem is that your app requires GPS (ACCESS_FINE_LOCATION) to accurately access location. That is, if a device does not have GPS, it is excluded from the compatibility list.

One solution is to use ACCESS_COARSE_LOCATION, which gets the location over the network. This form is less precise, but works for some applications (I do not know if this is your case).

Another way is to add the following to your %code% :

%pre%

Or:

%pre%

This informs that even your app needing GPS, you treat the situation for cases where there is not. Then, non-GPS devices can install your app.

Another detail that limits a lot of compatibility is the minimum version of Android ( %code% ) required to run the app. If the minimum version is very recent, you will probably delete many devices from the compatibility list.

For example, if the minimum version is API 14, Android 4.0, you can reach up to 90.4% of devices.

12/2014 Reference.

___

score 1 · Answer 1

1

Try using the formula = INDIRECT

Formula used in this example: CELL B3 of the table gestion.xls = INDIRECT (CONCATENATE ("["; B1; ".xlsx] Plan1! B1"); TRUE)

19.01.2015 / 18:19

score 0 · Answer 2

Here are some steps to accomplish what you're looking for here ...

Identify spreadsheets

From what I saw in your example, the name varies not only according to the date, but also the time, because in one of the files quoted put '0900' = '9:00 AM' and another with '1000' = '10 : 00 '.

1.1 If you have a pattern that only changes the date, it will be easier, because it would generate in the file 'gestion.xls' a routine to create the name of the files in order to get a specific date, the day or the previous day, if as the case may be.

' Para pegar o arquivo de hoje
="matriz_"&TEXTO(AGORA();"ddmmaaaa")&"1000"

Adapt the above example to search for the desired date at the desired time, and it may be a fixed time.

1.2 If you do not have this filename, you can create a specific folder that will have only the file you want to read and do a routine (function) to read all the files in this folder,

List all files in a folder

This code is quite complete and you can adapt to fetch from a fixed folder and fixed type '.xls' as needed.

Once you know which file has to be referenced, you can fetch the data!

Data search

2.1 The data search can be done directly as shown in your example, if it works as you wish, ok!

2.2 If you want to copy this data, you can have a temporary 'tab' to copy the data, work and write to the desired table. Depending on the complexity of the search, if you have filters to do etc. I use a direct connection via ODBC (Excel Files) to filter using SQL the data I want and the filters required. In some projects I have done I used this function below that can be adapted according to your demand:

Sub SQLPesq(ByRef WHAREHOUSE As String, ByRef PATHFILE As String, ByRef PATH As String)

' Macro para pesquisar no arquivo selecionado, dados do armazém selecionado

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=Excel Files;DBQ=" & PATHFILE & ";DefaultDir" _
    ), Array( _
    "=" & PATH & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
    )), Destination:=Range(WHAREHOUSE)).QueryTable
    .CommandText = Array( _
    "SELECT 'RESUMO$'.Clientes, 'RESUMO$'.'Descrição do Armazém', 'RESUMO$'.IE, 'RESUMO$'.'Tipo de Grão', 'RESUMO$'.'Total Geral'" & Chr(13) & "" & Chr(10) & _
    "FROM 'RESUMO$' 'RESUMO$'" _
    )
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "tbl" & WHAREHOUSE
    .Refresh BackgroundQuery:=False
End With

End Sub

After this search would have how to work the data, manipulate, format and write this information in the table, but I think it would fall outside the scope of what asked.

I hope I have helped!