Applying 2 formulae to columns of varying length

[WangDoodle]

I'm posting this as a separate thread because it is different than my previous question, although it will indirectly solve my previous problem!

30 worksheets each have the same 2 columns of data that get downloaded/updated daily. The columns have a title row. Column A contains the date. Column B contains the price on that date.

Column C is not downloaded, but currently contains a formula contingent on the price change between each row in B. The formula begins in C3 and is intended until the end of the column, reading as follows: =B3-B2, =B4-B3, etc.

Column D is also not downloaded, but contains a formula contingent on Column C. The formula returns a +1 if the price change is positive, -1 if the price change is negative. The formula begins in D3 and is intended until the column, reading as follows: =if(C3>0,1,-1)

Problem: The length of the columns for each worksheet will be the same on all 17 worksheets, but can vary enormously each day once data is downloaded.

2 Questions:

1) Is there a way to apply the formulas in Columns C and D in an automated manner, so only rows with dates and prices are calculated and delineated?

2) On each row for which there is a date and price for all the worksheets, I wish to sum all the -1s and +1s in Column D for the particular date on a separate, collective worksheet. So, if worksheet ABC has a price in B3, I wish to sum the 1 or -1 in Column D with worksheet DEF's column D, etc., all the way to end of the columns with data. Is there a way to perform this function in an automated manner, so that only the columns with numbers are calculated and delineated?

[WangDoodle]

Enclosed is the example workbook, with 9 sample worksheets, in lieu of the actual 30, containing two columns ( A and B) of downloaded data, and 2 columns (C and D) with the formula described above.

Not sure if it is needed, but the 10th sample worksheet, named VOW.de, is how the data appears after it is queried (i.e. without the formulas in columns C and D).

The worksheet named Summation contains one column with the number of advancing minus declining issues tallied by the summation of all the 1s and -1s.

I've deleted the query page from the actual spreedsheet in this example sheet because of the macros.

Many thanks.

[DonkeyOte]

Regards question 1, perhaps along the lines of:

Please Login or Register  to view this content.

Regards question 2 - if it's simply a 3D sum why not just use a 3D sum ?

Put a sheet "START" before first *.de sheet and another "END" after last *.de sheet and then just use:

Please Login or Register  to view this content.

[WangDoodle]

DonkeyOte,

Many thanks for your help. You always offer excellent solutions. But I've never entered a formula like the first. Can you tell me where I need to enter the formula and if there is any key sequence required to enter it properly?

[DonkeyOte]

You enter 3D formulae like any other "normal" function - if you follow the steps outlined you should be ok.

There are fairly significant limitations regards what you can do with 3D functions - ie SUM, AVERAGE etc... and they are non-conditional.

I suggested the 3D SUM given your sample is summing the same cell across all sheets - there is seemingly no condition governing the cells to be selected (other than their location which is consistent).

[WangDoodle]

DonkeyOte,

Sorry to be dense. I know the second formula may be applied wherever I wish and doesn't require any special steps. Does the first formula need to be applied to a particular worksheet between 'START' and 'END' or put in any other particular place on the worksheet applied?

[WangDoodle]

I successfully placed the second formula:

=SUM(START:END!D3)

after placing the START and END sheets as instructed.

But when I place the first formula (I am confused about where to put it) using an = sign preceding the formula, I get an error message. Is the first code designed to be functional exactly as written or must I alter it in some way?

Please help!

[DonkeyOte]

The first "formula" is not a formula - it's a VBA sub routine.

[WangDoodle]

OK, in case anyone else is as clueless as me (not likely).

To enter the VBA sub routine, I pressed ALT-F11 to get into VBE editor. Then I inserted the code in the first worksheet ("START"). Then I Hit ALT-F11 again. Pressing "run", the VBA sub routine performed exactly as intended.

However, when I update with the latest prices once again via query, all the rows in column D are transformed to '-1'. The VBA sub routine seems to need to be run manually each time. Is there a way to run the VBA sub routine automatically once the latest batch of prices is downloaded?

[DonkeyOte]

It might be an idea to outline the process by which the "batches" are downloaded - presumably external query of some sort - but are all queries refreshed simultaneously or is each refreshed separately ?

[WangDoodle]

Thanks for your response.

A macro drives the downloading of all 30 issues and worksheets simultaneously.

I say "issues and worksheets" because for each issue on the list for query (a list of the 30 issues is on a single worksheet), the macro creates a separate worksheet with the two columns of dates and prices for each of these issues.

The update for all issues happens at once, and covers 800 days. Today's update replaces the 800-1 previous days with the previous 800 days of closing prices.