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.


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.


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

  • 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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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