Parsing values out to separate summaries

[adenkha]

I have created a workbook to record assets purchased though out the financial year. There are 5 worksheets within the workbook, the Cover sheet (Where all details to be entered), 1ST QTR, 2ND QTR, 3RD QTR and 4TH QTR.

All the asset details are entered into the cover sheet and then these details (Whole row) need to be reflected on either the 1ST QTR, 2ND QTR, 3RD QTR or 4TH QTR worksheet depending on the value of the ‘Period Purchased’ column (drop down List) value.

Example: I purchased a drill on 20/03/2016 for $100 and set the value of the ‘Period Purchased’ to 3RD QTR. This information should then be reflected in only the 3RD QTR worksheet.

Can I have some assistance in creating the formulas? Your assistance will be much appreciated.

[JohnTopley]

Try

UMMERGE the cells withe Description in ALL sheets: simply increase the column width of C and Q

in P5 of "1st Quarter"

=IFERROR(INDEX('Cover Sheet'!$B$2:$G$1000,SMALL(IF('Cover Sheet'!$H$2:$H$1000="1st Quarter",ROW(A2:A1000)-ROW(A2)+1,""),ROWS($A$2:A2)),COLUMNS($A:A)),"")

Enter with Ctrl +Shift+Enter

Copy across and down

Repeat for other quarter sheets

[Glenn Kennedy]

and unmerge ALL the rows as well. You're only asking for trouble with those merged cells. one row = one line of data. then just adjust the cell depth to suit.

[adenkha]

Thank you for the above formula John. It works fine but I'm finding that if i insert 5 or more new rows in any for the 4 quarters worksheets the formula then stops working and shows blank cells even though there are values in the cover sheet.

Any ideas as to why this is happening?

Cheers,

Ash

[JohnTopley]

No idea so please post a small sample file (not image) showing the problem.

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[adenkha]

Hi JohnTopley,

As as per the attachments, without inserting 5 rows the values are displayed as per the cover sheet but upon inserting 5 or more rows just after the expenses the values disappear.

[JohnTopley]

I need a file NOT image please.

[adenkha]

Sorry mate. It has been attached now.

I've allocated 5 items to the 1st quarter from the cover sheet. If you view the 1st quarter tab you will be able to view all 5 items but if you insert 5 new lines anywhere below the asset purchases table (Line 11 and onwards) the values in the table will disappear.

Regards,

Ash

[JohnTopley]

I must be missing something: I delete then re-inserted the lines in "Cover Sheet" and the summary in "1st QTR" P5 onwards is still OK.

If I change the Quarters they appear in the right sheet.

[adenkha]

John,

Sorry i wasn't very clear. If you view the 1st QTR and insert 5 or 6 new rows or more in row 11 or below you will see that the values in the Asset table in the 1st QTR tab disappear.

Ash

[JohnTopley]

Sorry ... Me be being dumb!

Change formula to:

=IFERROR(INDEX('Cover Sheet'!$B$2:$G$1005,SMALL(IF('Cover Sheet'!$H$2:$H$1005="1st Quarter",ROW('Cover Sheet'!$A$2:$A$1005)-ROW($A$2)+1,""),ROWS($A$2:A2)),COLUMNS($A:A)),"")

enter with Ctrl+Shift+Enter

Copy across and down

[adenkha]

Thank you very much mate, worked like a charm.