Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Working with multiple tables and multiple arrays (Read 1747 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Working with multiple tables and multiple arrays
28.04.10 at 09:41:25
Working with multiple tables and multiple arrays
If I may, I have what I hope will be just one last question.  The array
formulas are working great, although I have one small hitch.  There are
three different tables I'm drawing data from, and roughly 20 cells in total
I need to populate.  For clarity, the columns are arranged so that the
different tables are not in consecutive sequence.  For instance, I have
something like this:

User DefinedPull from Table1Pull from Table2Pull from Table1Pull from Table3Pull From Table2

I'd like to set up three different array formulas and have each one pull the
needed data.  However, I don't seem to be having much luck selecting
different cells (holding the CTRL key to select multiple) and assigning an
array.  It doesn't seem to recognize each cell I've selected.  If the cells
are consecutive I don't have any problem.  I could possibly rearrange the
cells so that tables 1, 2, and 3 are next to each other.  My only hesitation
is that the spreadsheet contains things that happen in a sequence so it
might be a little more confusing for people to interpret.

I'll keep poking around, but thought I'd see if you knew of a method for
getting around this.  I believe this is an Excel thing so I'll keep reading
up on how to select non-adjacent cells and make them part of an array

This is an Excel limitation you run into. Array formulas can only given on a contiguous block of cells.  

  • Write one query (SQL) to retrieve all columns in one go.  
    When retrieving one row from more than one table and you need to do this in a single SQL statement you can use the following SQL technique. This would work in Oracle and in SQL Server:
    select na.a1, nb.b1, na.a3, nb.b2, na.a2, nb,b3
    from   (select a1,a2,a3 from table_a) na
    ,         (select b1,b2,b3 from table_b) nb
    When table_a and table_b are related and you want to ensure you do not get a cartesian product with many many records you can add where clauses to ensure the nested tables as these queries are called return only a single row and if they do return multiple rows you can join them using the where clause as indicated below.
    select na.a1, nb.b1, na.a3, nb.b2, na.a2, nb,b3
    from   (select a1,a2,a3 from table_a where id=1) na
    ,         (select b1,b2,b3 from table_b where id=1) nb
    where =
  • Put the formulas where you cannot see them (different sheet eg) and just use =address functions to 'copy' the values to your sheet.  
    So you could e.g. retrieve the columns from table_a to AA1:AC1 and the columns from table_b to BA1:BC1. Once retrieved you can simply point a formula to the results so you can place them where you want to see the result. E.g.
                A            B                 C                 D
    1          A1           B1               A3               B2
    2          =AA1       =BA1          =AC1           =BB1
Back to top
« Last Edit: 28.04.10 at 09:54:19 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1