Man of Honour
- Joined
- 5 Jun 2003
- Posts
- 91,783
- Location
- Falling...
Hi all,
I have an ODBC connection to an SQL database on excel. All the data is there, and updates nicely without issue.
Sheet1 = ODBC data
Sheet2 = Area to extract relevant date from Sheet1
In sheet 2, I want to extract only certainly columns and not the whole table as I want to be able to present the data a certain way rather than have the whole data listed (over 40 columns of data).
Now I guess I could do it with a Pivot Table which would be the simplest way (however, that limits my ability to tweak things a little (like apply certain formulas to represent the data certain way) – but I have to have more of a play). The advantage is that the data is refreshed automatically.
The current way I’m doing is doing a VLOOKUP – but the issue with that is I have to copy and paste the data from the column in database in sheet1 to have something to extract from the vlookup function – and whilst I could create a macro for it, I still have to manually add the entries to the bottom of the column. i.e. if the list is currently 1-140. Tomorrow the list may be 1-145. I could just fill the rest of the cells down to the bottom with prepopulated fields, and let the vlookup just give an #NA for the data that isn’t there, which can be mitigated with an IF(ISERROR) function. However this feels clunky and clumsy.
I guess what I’m asking, is there a way I can just extract a specific column of data from the ODBC connection, whilst keeping sheet2 dynamic (i.e. automatically updating as the data is updated). Or is a pivot chart the one and only way of doing it?
I realise this sounds garbled, but thought I'd ask anyway!
I have an ODBC connection to an SQL database on excel. All the data is there, and updates nicely without issue.
Sheet1 = ODBC data
Sheet2 = Area to extract relevant date from Sheet1
In sheet 2, I want to extract only certainly columns and not the whole table as I want to be able to present the data a certain way rather than have the whole data listed (over 40 columns of data).
Now I guess I could do it with a Pivot Table which would be the simplest way (however, that limits my ability to tweak things a little (like apply certain formulas to represent the data certain way) – but I have to have more of a play). The advantage is that the data is refreshed automatically.
The current way I’m doing is doing a VLOOKUP – but the issue with that is I have to copy and paste the data from the column in database in sheet1 to have something to extract from the vlookup function – and whilst I could create a macro for it, I still have to manually add the entries to the bottom of the column. i.e. if the list is currently 1-140. Tomorrow the list may be 1-145. I could just fill the rest of the cells down to the bottom with prepopulated fields, and let the vlookup just give an #NA for the data that isn’t there, which can be mitigated with an IF(ISERROR) function. However this feels clunky and clumsy.
I guess what I’m asking, is there a way I can just extract a specific column of data from the ODBC connection, whilst keeping sheet2 dynamic (i.e. automatically updating as the data is updated). Or is a pivot chart the one and only way of doing it?
I realise this sounds garbled, but thought I'd ask anyway!
