Generating a table in another tab! Please help!

[wilsoncj322]

Howdy,

Due to security at work, I am unable to link an example to my spreadsheet but I feel that I can explain it very well.

Column A consists of account names.
Column B consists of account balances.

I want to use a formula or something that would automatically generate a table in a new tab that only consist of the accounts with balance greater than or less than zero. Pivot Table would not work because once I get this table populated, I need to be able to do some work with it. Is this possible?

[wilsoncj322]

I should add, I am trying to create a spreadsheet that is universal that everyone on my team can use. Each one has different accounts and balances. I want something where they can input the balances on this first tab and then the formulas, and what not, then automatically populate a table.

[alansidman]

You could run an MS Query on the table.

Here is a link to show you how to do that.

http://www.exceluser.com/explore/msquery1_1.htm

Alan

[wilsoncj322]

Alan,

Thank you for the quick response but I do not think that is what I am looking for. The listing of accounts are balance sheet accounts with their balances. I am trying to figure out a way that on the new tab, it will only display accounts with balances greater or less than zero. Then, my co-workers will then do their reconciliations on that new tab. We are switching to a paperless format for reconciliations and I am trying to figure out a way for this listing of accounts and balances to populate automatically so they won't "mess" anything up.

[e.isayev]

The formula in sheet 2 takes the names which balances are not equal to 0
=IFERROR(INDEX(Sheet1!$A$2:$A$5,SMALL(IF(Sheet1!$B$2:$B$5<>0,ROW(Sheet1!$A$2:$A$5)-1),ROW(Sheet1!A1))),"")

[wilsoncj322]

When I input the formula into the spreadsheet, the formula checker says it is right and that it should display the account name, but nothing is showing up. Will this be automatic if someone that has more/less accounts and will it populate the same? We have a program that brings in our G/L numbers into Excel. However, they are not in the format that we like. I need my co-workers to be able to put this into a balance sheet format. They will only have the account name (which includes the acct number which lets us know what type of account it is) and balances. I would like to have something that will then take those account names and bring them to a new tab. Then have the balances matched with those account names. I want account balances that are zero to be ignored; therefore, debit or credit balances will be picked up. Then, if there is a way to have a search conditional format deal where when the asset accounts stop, then it automatically formats the balance column to where it sums all the previous balances and then formats that sum cell with a single border to the top and double underline to the buttom. I want to make sure that when we add/subtract an account, the spreadsheet automatically changes to make it easier on my co-workers. The less they have to manipulate, the better. Any suggestions?

[wilsoncj322]

Your formula may not be working properly. When I open your file and go to sheet two and then double click on all the cells in column A with values, you will notice that only the first account from sheet one is picked up while the rest are blank and #N/A for the balances. Did something change?

[e.isayev]

In sheet 2 I can see all account names which do not equal to 0. They are 3 of them

[wilsoncj322]

random.jpg

I uploaded what I am seeing. You can see that the formula is the same but not showing up properly for me.

[e.isayev]

Attachment 193910

I uploaded what I am seeing. You can see that the formula is the same but not showing up properly for me.

You enetered it as normal formula
Press Ctrl+Shift+Enter after entering formula and copy down

[wilsoncj322]

Okay, that worked and got the formula working. Question. Anyway to make this done automatically? Maybe with a macro and VBA? or even with a formula? Your formula gets me the information I need but I would like for this to happen automatically. If I were to add 20 accounts, then I would have to do more work, but if I had a way to do this automatically, then that would be less work. Any suggestions?