Database Function Criteria

Hello all. I'm working on a spreadsheet that separates out, based on an index, gains and losses for a given period. I have setup one worksheet that includes the periods and returns. I have also setup simple count functions (I thought I could use these in the DGET function) that count the cumulative gain/loss periods.

My problem is working with the DGET criteria. I've tried to search and find a way to list the criteria horizontally instead of vertically, but have not found any such way. I've attached the work file.

Basically, I want to show all the exact same data on the second workbook as the first, except only for periods of gain. Let me know if I'm not clear or if I need to further explain.

Thanks.

I am not a DGet or D<function> expert, but it seems like DGET is a painful approach.

Possibly the following will help:
- Copy the first row of your Gain functions to a safe place.
- Replace the functions in all data rows, in the Gain, Loss, *** Gain, and *** Loss columns, with the resulting values, by Copy, then Paste Special, Values (in place).
- Select all of the rows and sort into order on Gain.
- Select the rows of interest (now all together) and copy to another sheet.

- Replace the Gain functions when you want to re-analyze the data.

Repost if you need something more automated because you will need to do this many times.

See also your attached workbook with an example.

The goal of the spreadsheet is to be automated. Dget will work, however, I just need to figure out how to work with the criteria of the function. Normally, the criteria must be stacked... ie (A1 = Gain, A2 = 1) which will find the row where GAIN is equal to one. I have the datasheet setup to count so that I can reference each gain without empty rows on the gain sheet.

The gain/loss is derived only from the index data column. The idea is I can replace the index at any time, and the gains/losses data will reflect the index. This is basically the core of an up/down analysis for mutual funds/etfs I want to create.

The simple answer, if possible, is to write the criteria in the function, ie:

dget(Named_Database,column_of_info,["Gain"=1])

wheras the syntax for the criteria is actually valid. Or, if I can get the criteria to work horizontally, ie (A1:B1) where A1 = Gain & B1 = 1. If I could do this, I could just copy the Gain down the entire column, and count in the value column.

Thanks for your help!

Maybe someone has an idea of an alternate way to accomplish the goal?