Solved: Formula with { & } at both ends

[TedMosby]

From thread http://vbaexpress.com/forum/showthread.php?t=25651

xld posted on the example workbook I attached this formula below
{=MIN(IF((INDEX(Rota,0,3)=DSUMCriteria!$D$6)*(INDEX(Rota,0,1)=$C6),INDEX(Ro ta,0,COLUMN(F$1))))}

but everytime I write this out it just stays in the cell as text. How do you change it to a formula?

The reason I need do this is because I have moved so ranges around and its knocked the formula out.

I have attached a copy of what am doing so can see what I mean, looking at Sheet"Workload" Row 10 C & D

How do I change this formula?

[chungtinhlak]

where is it in your file? what tab? cell?

[TedMosby]

Its in Sheet "Workload" Row 10 C & D

[Simon Lloyd]

Ted, thats an array formula and you can't simply add { before and after, the formula must be "committed" using Ctrl+Shift+Enter the curly brackets will then surround your formula. If you ever edit the formula or click in to it again you will need to commit it again!

[TedMosby]

Thanks for that. I never knew you could do that :)

It has worked for my Start Time, but when I did the same for Finish Times, it keeps saying 00:00 instead of the Finish Time in the list of data. Where have I gone wrong there?

Start Time
{=MIN(IF((INDEX(Rota,0,3)=Lookups!$D$6)*(INDEX(Rota,0,1)=$C10),INDEX(Rota,0 ,COLUMN(F$1))))}

Finish Time
{=MIN(IF((INDEX(Rota,0,3)=Lookups!$D$6)*(INDEX(Rota,0,1)=$C10),INDEX(Rota,0 ,COLUMN(G$1))))}

I'm looking at Workload Sheet, Cells D10& E10 in the attached workbook in #1

[TedMosby]

Why would the formula array work in 1 column and not in another is in case with #5 in this thread?

[mdmackillop]

It returns 13 for me

[TedMosby]

Where have you got 13 from, I dont understand that?

I have tried it again.

Looking at the date the workbook opens is 26-Mar-09
Row9 is Barney Stinson. Start time 08:00, Finish Time 00:00

If you look at Barney Stinson on the Rota Tab again Row9 Start Time is 08:00 and Finish Time is 17:00

I thought the fomula array would pull back the Finish Times for the Column Finish Times on the Workload Sheet tab.

[mdmackillop]

Here is my test with your original sample and the formulae from Post 5

[TedMosby]

Thanks, my problem is that the Finish Time you have got as 13. Once converted to a time like the others it still shows as 00:00 and not what time is on the Rota tab, which is what I want to show.

On the Rota Sheet Tab Column G shows the Finish Times. This is what I want to Display in Column E under the Workload Sheet Tab in Rows 9 to 108. The Start Times show as they do in the Rota Tab, but why does the same formula show them all as 00:00 for the Finish Time.

[Bob Phillips]

Because the Rota range starts in column C, not column A, you have to adjust your column offset to cater for that.

So your formulae should be

=MIN(IF((INDEX(Rota,0,3)=Lookups!$D$6)*(INDEX(Rota,0,1)=$C9),INDEX(Rota,0,C OLUMN(D$1))))

and

=MIN(IF((INDEX(Rota,0,3)=Lookups!$D$6)*(INDEX(Rota,0,1)=$C9),INDEX(Rota,0,C OLUMN(E$1))))

[TedMosby]

I see, so does the offset start from Column A or now Column C?

Sorry, I'm not 100% sure on what you mean around this bit

INDEX(Rota,0,COLUMN(D$1)))) why D1, because in the Rota sheet that is the Day of the week and not the Start Time, or am I missing the point?

[Bob Phillips]

D1 because the Start time is in column F, which is the 4th column in the named range Rota, and D1 points to the 4th column. The named range Rota start at column C NOT column A.

[TedMosby]

Ahh I understand now. Excellent. Thanks for that :D