Function_num

[Moti]

Can anyone help me with this function?:

its part of the subtotal syntax for including or excluding hidden rows:
=SUBTOTAL(Function_num,[ref1],[ref2]) (9)

Is there a way to get this function to exclude hidden columnn as well.
currently it only works to exclude hidden (grouped) rows.

Thanks.

 

[Mark W.]

Suit yourself. This thread has been ongoing for over 5 days now. Something must not be so simple.

 

[Fazza]

Moti,

I haven't looked carefully at the structure of the source data on different worksheets - don't even know if you've described it fully. Regardless, Mark's comments are spot on.

Get smarter with the data structure and life becomes much easier.

Pivot tables have powerful built-in functionality. Use it if you can.

FWIW, I wouldn't be pulling data to the main sheet using VLOOKUP. If you want to a smarter way consider using a query table. Give each source data range a defined name, not dynamic, and then you can follow the wizard - via data, import external data, new database query. Into a pivot table if you want. It might be valuable to develop some knowledge of such techniques.

Otherwise, write some VBA to do what you want. Maybe even look at running some reports from Access. There are, usually, many ways.

cheers,
Fazza

 

[Fazza]

Moti,

Some further comments about getting data from multiple sheets. I'll start with the manual method. Assuming the spreadsheet has been saved and for simplicity that each worksheet has data in exactly the same fields. It need not, it just simplifies the explanation.

Give each data range a defined name. Not dynamic. Say, Table1, Table2, Table3, etc.

Then menu Data, Import External Data, New Database Query. Select the Excel file, then Table1 field/s.

For this one, we won't filter. Or sort. Select the option to view & edit in Microsoft Query before hitting FINISH.

You should see the field/s from Table1 and the first screenful of records.

Now hit the SQL button (or View, SQL) and edit directly in the window to obtain,

SELECT *
FROM don't change this bit.Table1 Table1
UNION ALL
SELECT *
FROM don't change this bit.Table2 Table2
UNION ALL
SELECT *
FROM don't change this bit.Table3 Table3

Where after the first two lines (SELECT * FROM whatever) there are groups of three lines per data range. Each group being of form
UNION ALL
SELECT *
FROM don't change this bit.TableX TableX

You can copy & paste in the SQL window.

When this is done, put the data in Excel via the file menu or the "door" icon on the toolbar.

You've now created a simple consolidation of the mutliple ranges.

Refresh like a pivot table. Data sources could be on closed workbooks - and is still quite fast. When you know how to change the SQL, can develop powerful queries. This whole thing can be done in VBA (created from scratch on the fly) and the SQL is easily editable - it is simply a string. You do have to be careful with the punctuation/syntax. If the table is created manually, it is just a single line of code to refresh the table.

Hope this whets your appetite. If you search there are a couple of other recent posts where I've described other examples of query tables.

regards,
Fazza

 

[Moti]

Thanks again Fazza, it seems to me that I am a bit out of my depth here.

My knowledge is severly limited and I generally find creative ways of working round problems wth the excel skills that I have.

This is probably invaluble to me, but i need to go back to the basics.
I looked up all your posts and in-fact maaged to import my data, join tables and select (from sigma) the various functions. However the result when returned to excel was not even close to what i was looking for or expected

I guess I am going to get a copy of excel for dummies on my way home from work.

The sad thing is that the way I am working with this workbook is crazy considering easier ways to get the same work done.

The workbook currently has 30 worksheets and it really inefficient. But your solutions are a little too advanced for me.

Thanks again. You really have a lot of patience.

 

[Fazza]

Thanks Moti. And good on you for working through the query table examples.

Back to basics sounds smart. Maybe a fundamental flaw is the structure of the workbook? Instead of 30 worksheets, any chance of putting it all on one? Or even using Access for some of the data storage. Excel & Access interlink seamlessly.

Developing your skills is the best investment.

Regards & best wishes,
Fazza

 

[Fazza]

Moti,

You asked in a pm about some further info on this. I've just found the very well presented information at Tushar Mehta's site.

http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

regards,
Fazza

 

[Fazza]

Moti wrote

I looked up all your posts and in-fact maaged to import my data, join tables and select (from sigma) the various functions.

Moti, the sigma button was not part of the process described above. Without pressing it you might have returned a single consolidation from the various input sheets. From there, you can run either further queries or pivot tables.

This whole approach would raise more complications and questions - and until you are familiar with it might not be workable for a novice.

It can be coded in VBA and when I do this sort of thing always create queries on the fly.

When you learn a little SQL it can be surprisingly simple & powerful.

regards,
Fazza