Index/Match Formula Disappears after Ctrl-Shift-Enter

  • I have a frustrating problem with Index/Match that I'd appreciate guidance on.
    I use Index/Match to automatically transfer data between two workbooks. The destination workbook has several worksheets and I want to use Index/Match to pick up data from the source workbook for use on four of the destination worksheets.
    I currently have one destination worksheet working fine, however when I try to use the exact same formula on a second destination worksheet the formula disappears as soon as I hit Ctrl-Shift-Enter. I've tried entering the formula in the second worksheet using copy/paste from a text file; I've typed it in from fresh. Neither method works - the formula disappears as soon as I hit Ctrl-Shift-Enter.
    Can anyone suggest why this is or, more importantly, what I need to do to get the formula to stay in place.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    What is the Index/Match formula that works, and what is the name of the sheet on which it does work?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    The formula that is working fine is:
    =IFERROR(INDEX('[H+SS_Variable_ISLh1_v3.xlsm]H+SS Filter'!$D$8:$D$1063,MATCH(F9&G9,'[H+SS_Variable_ISLh1_v3.xlsm]H+SS Filter'!$E$8:$E$3000&'[H+SS_Variable_ISLh1_v3.xlsm]H+SS Filter'!$F$8:$F$3000,0)),"")
    The IFERROR was added in because the source sheet has hourly data and the destination sheet has half hourly data, so every alternate cell in the destination column was showing a #N/A error. IFERROR gets rid of that problem.


    The destination sheet on which it works is 'RT-Up Template'. The sheet on which it doesn't work is 'RT-Down Template'.


    A difference between the two formulae is that RT-Down Template needs to source its data from $B$8:$B$1063 on source 'H+SS Filter'.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    Generally it is not good practice to reference other workbooks in formulae, it often leads to problems. Have you tried simply typing the required formula into the 'RT-Down Template' sheet rather than copy/pasting?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    Unfortunately I've got no choice other than to reference the other workbook although, as a last resort, I can copy the source sheet values into a new sheet in the destination workbook. I was hoping though that Index/Match would simplify that process.
    Yes, as noted in the opening post I have typed it in, and I have copy/pasted it. I took the precaution of doing the copy/paste from a text file, rather than from the originating worksheet, specifically to avoid possible problems due to hook-ups with other worksheets.
    I'll create a new sheet in the destination book and transfer the source data into there, then see how it goes with everything in the one book. I'll update the thread once I complete.
    And in case it helps with understanding my worksheets, source column E is date and col F is time; destination col F is date and col G is time. Source columns B & D each contain a code letter based on filter criteria.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    An hour or so of testing has produced no improvement. I did as indicated above: I created a new worksheet in the destination workbook and copied into it the values of columns A to F of the source worksheet. I then typed in the formula:
    =IFERROR(INDEX('Filter'!$D$1:$D$1056,MATCH(F1&G1,'Filter'!$E$1:$E$1056&'Filter'!$F$1:$F$1056,0)),"")
    and once again the formula disappeared after Ctrl-Shift-Enter.
    I've tried with and without IFERROR.
    I've tried with and without sheet names ahead of F1 & G1.
    I've double checked that the range details are correct.
    I don't know what else to try.


    As a work-around, could you perhaps give me VBA coding that will insert a blank row after every current row, all the way down the column. That way I can convert hourly data into half-hourly format and copy/paste the values from one worksheet to the other. That way:
    [ATTACH=CONFIG]72762[/ATTACH]
    will become:
    [ATTACH=CONFIG]72763[/ATTACH]
    and will then align with the layout of the destination worksheet. Maybe not the most elegant, but it sure beats manually transferring hundreds of entries from one sheet to another.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    That is a possible work-around, but far better to fix the formula issue.


    Can you attach a sample workbook, with the newly added sheet. It need not have the full dataset but at least enough rows of data to be truly representative.
    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    OK - I've scaled back the workbook and it's now 200 or so KB, however there's not much point in posting it because by destroying 90%+ of the original book I've also destroyed the cause of the problem. I can now enter the Index/Match formula into both worksheets and both of them work correctly. Now whilst that in itself is good news in one respect, it still doesn't pinpoint where the problem is. To get from a 10 MB file to a 200+KB file I've deleted graphs, complete worksheets, thousands of rows and columns of data, ...etc... and the resultant little file is of no use to me other than having proved that my workbook is the cause of the problem.
    I'd like to take a 2-pronged approach from here. Firstly, if you don't mind, I'd still like that bit of coding to enable me to add in blank rows. That will allow me to complete an exercise that I was part way through doing. With that done, I'm then going to totally rebuild the workbook as it's the end result of over 6 months work and has to be made both functional and without buried errors.
    Meanwhile, thank you once again for your patience and assistance. If you do want it, I'm happy to post the shrunken but now error-free workbook,

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    Assuming that Row 1 is a header row and data starts in row 2 & column A, then this code will insert an empty row between each row of data.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Index/Match Formula Disappears after Ctrl-Shift-Enter


    Many thanks for that. I won't have a chance to use it for a couple of hours but I've no doubt it will do the job nicely - if not, I'll get back to you.


    Thanks again for the multiple solutions you've provided me.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!