+ Reply to Thread
Results 1 to 11 of 11

Linking formulae to specific cells between tabs in Excel

  1. #1
    Registered User
    Join Date
    10-16-2020
    Location
    London
    MS-Off Ver
    Professional Plus 2013
    Posts
    5

    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

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,682

    Re: Linking formulae to specific cells between tabs in Excel

    Are you using INDEX, MATCH to get the value?

    Using MATCH can avoid refence changing from inserting column/row.

    See atachment
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-16-2020
    Location
    London
    MS-Off Ver
    Professional Plus 2013
    Posts
    5

    Re: Linking formulae to specific cells between tabs in Excel

    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.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,879

    Re: Linking formulae to specific cells between tabs in Excel

    Hello captainmerky and Welcome to Excel Forum.
    It may help us in our attempt to resolve your issue if you could upload an illustrative sample.
    Instructions for uploading an .xlsx file are provided in the banner at the top of the page.
    Let us know if you have any questions.
    Last edited by JeteMc; 10-17-2020 at 08:31 PM. Reason: edited text
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    10-16-2020
    Location
    London
    MS-Off Ver
    Professional Plus 2013
    Posts
    5

    Re: Linking formulae to specific cells between tabs in Excel

    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.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    839

    Re: Linking formulae to specific cells between tabs in Excel

    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.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,879

    Re: Linking formulae to specific cells between tabs in Excel

    Please paste the following into cell H2 and then double click the fill handle to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Edit: Here is another formula that will yield the same values and may be computationally more efficient:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Last edited by JeteMc; 10-17-2020 at 08:37 PM.

  8. #8
    Registered User
    Join Date
    10-16-2020
    Location
    London
    MS-Off Ver
    Professional Plus 2013
    Posts
    5

    Re: Linking formulae to specific cells between tabs in Excel

    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.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,879

    Re: Linking formulae to specific cells between tabs in Excel

    Try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  10. #10
    Registered User
    Join Date
    10-16-2020
    Location
    London
    MS-Off Ver
    Professional Plus 2013
    Posts
    5

    Re: Linking formulae to specific cells between tabs in Excel

    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

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,879

    Re: Linking formulae to specific cells between tabs in Excel

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. if function for finding specific cells in formulae
    By Tassss in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-22-2015, 04:55 AM
  2. [SOLVED] Linking tabs and formulas based on input into cells
    By petelomax in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-18-2014, 09:57 AM
  3. Linking tabs and formulas based on input into cells
    By petelomax in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2014, 08:24 AM
  4. Excel tabs linking
    By MistralAstral in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 06-17-2013, 06:13 AM
  5. Linking data to different tabs in excel
    By johnlol in forum Excel General
    Replies: 4
    Last Post: 02-27-2010, 11:41 AM
  6. Linking Cells through formulae
    By philipjbath in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2007, 04:02 PM
  7. Getting range of cells with specific text colour, then using in a COUNTIF formulae
    By cheekyflash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2006, 08:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1