help making a summarized table

[bassethound]

Hello and thanks for reading.

Im not one to usually ask for help but after google'ing for 2 days, I'm stuck and need help. This is probably very simple for most excel users.




Column A contains Dates of the year

Column B is a deposited cheque amount

They are both part of a table.

In another table Ive create, F and G, , I need it to collect only the dates with the deposits.

Screen Shot 2017-01-04 at 10.00.53 PM.png



Thanks again for any advise.
Mat

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[bassethound]

Screen Shot 2017-01-04 at 10.00.53 PM.png

I just tried to attach an Image

[David A Coop]

I found this formula:
=IFERROR(INDEX(A$2:A$15,SMALL(IF(ISBLANK($B$2:$B$15),"",ROW($B$2:$B$15)-ROW($F$2)+1),ROW(F2)-ROW($F$2)+1)),"")

Place it in F2 and copy down and across.

NOTE: It is an ARRAY formula, so you have to enter it with Ctrl+Shift held down. It will then appear in the formula bar with "curly brackets"

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[bassethound]

Wow, thanks for the quick reply David.

Ill be trying this out shortly, Im just finishing my year ends entries.

Im sure Ill have more questions :P

thanks again

[bassethound]

Hi,

So I finished my yearly data inputs and my spreadsheet got a little more complicated. I tried to work off your formula using it as a guide but just can't figure it out.

On my spreadsheet, I have a summary sheet. On the summary sheet I have a table in the L and M column. This table is meant to show me all the invoices I've ever received along with their date in order. (2016 tab, april 26, I received 185.94$) That should be the first one on the list... and so on. I plan to make a graph with this table showing me amount invoiced every month to see if my work load is increasing.

I can't upload the actual excel sheet so here is three screen shots of my 3 pages.

Sumary.png
2016.png
2017.png


thanks
Mat

[Pete_UK]

I find it very difficult to read those screen shots, but even if I could I would not like to try to enter all that data into a workbook BEFORE being able to do any work on it, so it would be better if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Hope this helps.

Pete

[David A Coop]

Hi Mat,

I saw your private message an I'm waiting to see the actual file.

As Pete says, it is not easy working with pics

DAC

[bassethound]

attempt #1 to attach excel sheet

[bassethound]

Ha!

Thanks Pete

Keep in mind Im no pro. This took way too many attempts and hours of "googling" to get to where it is now. I like it, it works for me.

The whole point of doing this is because I wanted to make a graph showing the growth of the company ( Amount invoiced per month)

To be more specific, Y axis would have $ and the x axis would have year and the month. and this "line graph" would keep current, like my pie chart.

Hope that explains it well enough.

If theres any questions, ill try to reply quickly. And I'm open to ideas to make this sheet better.

thank you

[protonLeah]

You can add another table using:

Please Login or Register  to view this content.

to sum by month number (first column would be first day of each month)

[bassethound]

Arg. I just noticed my last reply never posted.

Well, Ill start off with thanking Leah. Its pretty much what Im looking for. What I don't understand is why did you have to make a new sheet. I was envisioning a table that pulls the deposit amount and date automatically from the big tables, both years. Also, I made the second table underneath the pie chart in the summary tab. I was hoping for the graph to show up there. But again thanks.

Does anyone else have any suggestions?

Thanks