Linking formulae to specific cells between tabs in Excel

Hello. New to this forum so hope I can get some help with an Excel problem I'm trying to resolve.

I have a spreadsheet which I use to update salary postings from month to month. From the original data, I have created on a secondary tab, another spreadsheet which has to be configured in a specific format to be uploaded into our accounting software (Sage 50). As the salary data changes every month, I insert columns and populate them with new data but when I do the inset, my formulae on the secondary spreadsheet change and continue to be linked to the original data. I would like the secondary tab to link to exactly the same cells so that whatever I put into a cell is always picked up after I have done the insert. For example whatever I put into cell C1 is carried over to the secondary sheet after I have inserted my new columns. What's happening is that this automatically changes to the contents of F1, the location of my original data.

I've tried locking the formulae and using index functions but whatever I do, after the insertion of columns, my formulae are shifted and continue pointing to the original data and not pick up the data from the new cells. My aim is to automate the process so I want it to be reliable!

I hope I've explained this properly! Can someone suggest a solution? Many thanks.

Captainmerky

Thank you bebo021999. That's worked a treat and such a fast reply was really appreciated. I had tried the INDEX function but hadn't tried combining it with MATCH so that was a great tip.

My data source contains values in 2 separate columns - Debits and Credits all shown in positive values. When I drag the formula down it returns a zero for the empty cells so I would have to point the formula for the credits to a different cell to return the required values. I imagine there is an IFERROR function (or IF-THEN or something else) I could use to say "if the value returned is Zero then return a value from a different column. I want to avoid changing formula in the column. Can you help with this please? Tried a few things but not getting te result I need.

Thanks again.

Here is an example spreadsheet of what I would like to achieve. I would like to get a formula that picks up data from both columns without having to change the column reference on the Salaries tab.

Thanks in advance.

If you always want to refer to cell X99 in worksheet FOO, use =INDEX(FOO!$1:$1048576,99,24). If you need to use a MATCH call to find the row, use =INDEX(FOO!$1:$1048576,MATCH(x,y,0),24) to refer to column X in the topmost row in which x appears in y.

Hello JeteMc,

Thank you for these solutions. They both work but there is one problem that they don't solve.

The original INDEX MATCH solution kept the columns from which figures are returned from the Salaries tab to 3 and 4. When I insert a new month's data, I automatically get the values for the new month on the Journal Template sheet from these columns. Your formulae automatically change when I insert a new month on the Salaries tab and return the data from the previous month. I would have to alter the formula each time which is what I am trying to avoid.

I've tried changing your INDEX MATCH one by inserting columns 3 and 4 into the formula (like they are in the one that works) but that broke the formula. Any thoughts on how that might fix my problem?

I'll keep playing around with your suggestion and will let you know if I have a Eureka moment! Thanks for the much-appreciated help.

Hi JeteMc,

Amazing! You did it. Have to say I've never come across the INDIRECT function before so thank you for introducing me to it.

Thanks to all who have contributed to this thread. No doubt I will call upon your expertise again in the future.

Captainmerky