Can a formula be moved down automatically

[Kelliebell]

Hello All,

I am working on a project for my boss who knows I love to solve impossible Excel problems.

Here are the basics -

Tab A lists any F&B Purchases we make, Tab B Lists any transfers to other departments that pay for the food

What we want to do is whenever a entry is made in the Transfer Tab - an automatic entry is made in the Purchase tab to show a Credit.

I have created an IF formula that does that - here is the problem though.

New entries are constantly being made on Tab A so I can't pin down an exact cell to put the formula in. Is there a way to move a formula down to the next available blank line so that it picks up the entry from Tab B? And then once an entry is made in the B Tab, the formula in the A tab would change to read information inputted on the next line down in the B Tab.

I thought about creating a Macro but I'm not quite sure where to start with it.

[jason.b75]

If you format your data as a table then it will resize to inlcude any new rows of data added to the bottom. Any formulas in the table will automatically be copied to the new rows.

Dynamic ranges are another thing you could look at.

If you need more detailed help then a sample workbook, (not a picture of one) with any confidential info removed / fictionalised, showing before and after would be of use.

To attach a sample workbook, click the Go Advanced button (bottom right), then scroll down and look for the Manage attachments link.

Also, please edit your title to something more meaningful, that briefly describes what you want the formula / macro to do. This is a requirement of the rules that you read and agreed to when you joined. Hang on... You did read them, didn't you?

[FDibbins]

welcome to the forum

Sounds to me like you need 2 columns in A - 1 for entries and 1 for transfers?

For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

I have changed your title for you - this time