• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Table Formula to automatically fill in when adding a row

I created a lookup formula on a Table.   It is pulling from another table to populate the field.  The formula works fine however, I would like to populate on the next row.  What am I doing wrong.  I have attached a sample file.

=INDEX('Bank Info'!$B$7:$E$10,MATCH([@[Account Number]],TblBankAccount[Acct number],0),4)

Thanks, Eric
Index-Match-in-a-Table.xlsm
0
ekaplan323
Asked:
ekaplan323
  • 2
  • 2
  • 2
  • +1
1 Solution
 
NBVCCommented:
Not sure what you mean by
I would like to populate on the next row
.  Can you elaborate?
0
 
etech0Commented:
You can use control-d to copy the formula from the cell above.
0
 
etech0Commented:
Or you could do something like this:

=iferror(INDEX('Bank Info'!$B$7:$E$10,MATCH([@[Account Number]],TblBankAccount[Acct number],0),4),"")

and copy it down to all the cells down to 500 or whatever. It will show blank unless there is a result
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
There is an option in Excel Options > Advanced for:

"Extend data range formats and formulas"

Ensure this is enabled.

If the area in question is "defined" as a table, it should do it automatically.

Thanks
Rob H
0
 
ekaplan323Author Commented:
Rob,

I think this is the issue, can't find the option in Excel 2010 Options Advanced.
0
 
Rob HensonIT & Database AssistantCommented:
See attached.
Excel-Options.png
0
 
ekaplan323Author Commented:
Solved the problem
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now