Populate matrix using database information?

**starnz** · 07-28-2012, 09:55 PM

Hi,

I have populated a database(Expense) with 4 columns(Date, Description, Amount, Type) listed by date over several months. I have another tab(Expense A) where I would like this information to be displayed in matrix form by month. The matrix is based on the different Types, 7 in total. So the matrix has 10 columns(Date, Description, 7 Types, Subtotal) and would be listed by Date as well. My goal is to easily show the amount spent among the 7 Types on a month to month basis using the matrix and an chart.

My question is how can I use the info from the database to automatically populate the entries into the matrix. For example, if the database shows a Marketing entry in the Type column I would like that Marketing info(Date, Description, Amount) to populate in the matrix.

I understand this represents a redundancy of info but if anyone has ideas to accomplish this it would be greatly appreciated! I have attached the file for reference...

**dilipandey** · 07-29-2012, 02:02 AM

HI Stranz,

See the below thread where I have provided the similar solution which you can customize to suite to your requirements:-

http://www.excelforum.com/excel-form...t-by-date.html

Regards,
DILIPandey

<click on below 'star' if this helps>

**starnz** · 07-29-2012, 11:41 PM

Hi DILIPandey,and thank you for the response.

After looking at the attachment you provided earlier I can see how it would do exactly what I need. If I were to create a dropdown with dates under the Date column in Expense A, I could use it to pull the corresponding info based on date from the Expense tab. However, I'm not able to follow your IF formula from the LeaveData tab. I don't understand how it pulls data from the Guide tab without referencing it in the formula. I am somewhat of an excel novice so please excuse my request for more information.

Thank you

---------- Post added at 11:19 PM ---------- Previous post was at 11:08 PM ----------

I think I understand everything but the CCRinfo entry for the array table in the IF formula. Does this somehow search the whole workbook, I'm not familiar with this entry.

---------- Post added at 11:41 PM ---------- Previous post was at 11:19 PM ----------

After another look, I see a slight difference between your example and my problem. In the example, the VLOOKUP is displaying the database info, as it is, only in another location based on the name input. What I am trying to do is display the same information only in a different way. If I could use my dates similar to how you used the names, i would like to populate the description similar to how you did. But then I would like the amount from the database to populate in the appropriate column in Expense A based on the Type from the database. The Types from the dropdown in the database are matched to the 7 Types from Expense A in the Income A tab.

**starnz** · 07-30-2012, 08:47 PM

I think I finally understand what you were referring to as a possible solution to my problem. I looked at your example again and realize your IFERROR formula would work for me if it were customized a little. Unfortunately, I can't even follow the example given as I haven't even seen many of the components that make up the IFERROR formula before.