Copying data from one sheet to another

I am using both open office and office 2007 and need the spreadsheet to work in both programs. Right now it works fine for getting the data out of the data tab(tab = worksheet) and putting it in the Monthly Report tab. In the Data tab, I have columns that go Index, Month, date, account number, account type, Notes, incoming expense, outgoing expense. The Month column automatically changes to the month depending on the date entered in the date column. The month column is then used in the Monthly report to find the data based on the month entered.

The Monthly report tab will show the current and annual expenses based on the month entered at the top of the report. What I would like to do in the 3rd tab is take the data from Data tab and list it in the 3rd tab with just the date, the notes, and the expenses. I would like to be able to say which month I want to display. So if I say 5 in the month, then it will display the data for the 5th month but I have not had any success in figuring out how to do this. I can get the first date in the month to show up but that is it. If I have more than one day in the month, then how do I get this information. I need to be able to do this without macros since I am using two different spreadsheets.

Thank you for your help

Ok, I kind of have this working by cheating on the date. I made 31 rows with that use the Date formula getting the year and month from another cell location and adding them to the day to determine which day of the month I will look for in the data worksheet. I then use the VLookup to find the dates in the data worksheet and add the values to the comment or price tab in my expsene report worksheet. My problem now is what happens when I have two or more of the same dates. It lists just one of them in my worksheet. Is there anyway to get the data for both dates? for instance I have 8/12/2010 and 8/12/2010 but each one has a different set of data for it. Can I get both sets of data using a formula or do I need a macro to do this?

I posted a sample file to show what I am trying to do. The two tabs that I am trying to work with in this case is the Data tab and the Expense Report tab. Look at the April 15th date to see what is happening.

Hi Who,
VLOOKUP can't find more than 1 lookup with different content.
So I just suggest, for this one not to lookup based on the Date, but based on the other, for example the Account.

And another,
Could you please tell me how you make the description in sheet of MonthReport cell E2?
I'm curious, it's not comment. But what is that and how to make it like that? :P

Blastranger, Thank you for the tip. I was afraid that that might be the answer so I will have to play with that and see what I can come up with. As far as the MonthReport cell E2, I am not sure what you are asking about but the E2 cell is just the month number with the cell colored yellow. If you change the number, it will change what month it will use to look up the data.

The description "2010-4" is simply the formula C2&"-"&E2 which combines the year in C2 with the month in E2 and adds a hyphen between them. A similar process is used for the title above the "2010-4".

Now on the expense report tab in the date section, I use the CHOOSE formula to match the Month name with the Month number so that If I choose 4 in the month, it will display April and if I choose 5 it will display May.

I hope this answers your question. If not, please tell me more about what you are looking for.

No, that's not what I'm trying to ask.
I mean the cell E2 of Sheet MonthReport when clicked (when selected) there is a box pop up at the bottom of that cell explaining that the cell need to fill number for Month. How you make the pop up box ?

Hi Blastranger,
It is called Data Validation. I am not sure what version of excel you are using but in 2007 version, if you place the cursor in the cell that you want to show the pop up box and then go to the Data tab, Data Validation button, you can select three different tabs of data validation. I am guessing in the earlier version, the data validation is under the Data menu.

If you just want some help text to show up saying how to enter the data, then use the second tab called "Input Message", and fill in the Title and message boxes with the data that you want to show up. I think there is a limit to the amount of data that is listed because the account cell in the data tab would list all of the available account numbers and text for them in OpenOffice but they are not there in Excel and when I try to add them, I can get so far before it stops allowing me to enter data. I guess Microsoft needs to take a lesson from Open Office and not limit the data, then again if it gets too long, it is no longer useful so maybe Open Office should limit their data.

Now if you want something even cooler, look at the month in the expense account section. You will notice when you click on E2, a drop down list is created. This is using the first tab in the data validation called "Settings" and it is set to allow list with the numbers 1 through 12 in the source section. The allow blank is disabled so that it will never show or allow a blank cell and the in-cell drop-down list is enabled. It is pretty cool.

I hope that helped. Also I was able to use the index column to pull the data that I needed. I will post the updated file later on.

Waw hey,
That is the answer I want

and about the Cool drop down to pick month, it's really cool after you pick all the date also changed and also description, great work! but there are bug, where if user type value other than 1-12, all the formula broken.

Maybe next time I will try implement that on my excel file also :p

Thanks for the information on the bug. I had not thought about that or even tried it since I don't have a lot of people that are going to use this, however I have now activated the error alert so if someone does try to enter an invalid number, it will cause the error message to appear and they will have to enter the message again. I only wish I could get the blank rows in the Expense report to hide automatically without having to use a macro. Oh well, for now I just have a local macro created where I can press ctrl-H to hide the selected rows or alt-H to show the selected rows.

I have posted my working version now if you want to see what I did.

One way would be to use Autofilter on the data area (index lines 1 to 79) and filter the date column on non-blank. You'll need to put a blank line before index line 80 to separate it (the Total row) from the filter area. You can either hide the new blank row or format it so the height is very small.

I can't see a way of automating it without macros.

Regards