Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to automate my company budget in Excel. The scenario is, On Sheet 1 I have all the account codes which refer to different account type, e.g.

500101
500102
500103
500105
700101
700102
710000

All 5 series refer to expenses and 7 series to recoveries. On Sheet 2, I have a specific calculation related to these account codes.

What I am looking for is: Pick a specific range of account codes from Sheet 1 to sheet 2 automatically, and if there is any change in the future, it should updated automatically.

For example, I want to extract all 5001?? account codes to Sheet 2.

500101
500102
500103
500105

If 500104 added afterwards, it should be updated on Sheet 2.

share|improve this question
2  
I see you already posted your question on Stack Overflow. Please do not cross post. For the future, remember that a question can always be migrated. (Oh, and next time you're copy/pasting, make sure you don't copy too much…) –  slhck Jun 8 '12 at 17:12
    
What methods have you tried so far? –  Ellesa Jun 9 '12 at 14:02

1 Answer 1

A VBA-less solution could use Excel's Table and Pivot Table functionality.

  1. Create a table on Sheet1 of all your values. I would also add a second column for just the first number of that account code (=left(account_code),1).

  2. Then use that Table to create two Pivot Tables, one filtered for the "5" codes, the other for "7" codes.

  3. Then set your Pivot Tables to refresh upon open or every X minutes and they'll keep up to date for you.

Or, you could always use VBA, or a lookup/indirect formula.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.