Excel: Inserting a column into an existing INDEX, MATCH, OFFSET formula ... how do I make the formula work?

I have an existing formula that works well in my example (attached.) However, when I insert a column in the spreadsheet, it throws it off. I also have another set of data that refers to original data cells that is not adjacent as the first set is.

Please look at the attached and let me know if I can insert a column, as well as another set of reference formulas.

Thank you.
Schedule-EE-2.xlsx
Cactus1993OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You will need to offset +1 for column in case you insert a column between B and C.

=TEXT(INDEX($A$9:$A$60,MATCH("+",OFFSET($B$8,1,MATCH(R16,$D$3:$I$3,0)+1,52),0)),"[$-409]h:mm AM/PM;@")&" to "&TEXT(INDEX($B$9:$B$60,MATCH("+",OFFSET($B$8,1,MATCH(R16,$D$3:$I$3,0)+1,52))),"[$-409]h:mm AM/PM;@")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cactus1993OwnerAuthor Commented:
I'm not sure what you mean ... sorry, I'm a little less advanced in INDEX/MATCH/OFFSET formulas.

It also doesn't seem to work with the "Tuesday" schedule set of data. Thanks.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That formula is referencing only the Monday data, not the Tuesday data.
Do you get the correct Monday Timings after inserting a column between B and C with the suggested correction in the formula?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Cactus1993OwnerAuthor Commented:
OK ... I got the part for the offset +1.  But how do I run the same formula for the Tuesday set of data? The in/out times are not adjacent to the data, and I'm not sure how to adjust the formula.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The expert who suggested you this formula only saw your Monday data, so suggested this formula.
Actually you need a formula which is dynamic in nature to pick the Day as well as the Name dynamically to get the correct output.

Let me see if I can suggest you something which you can work with.
0
Cactus1993OwnerAuthor Commented:
It doesn't have to be too complex, meaning, if it is complex to be dynamic to pick the day and it's easier just to write a hard OFFSET formula, I can just copy it for each day. As you can imagine, my spreadsheet runs the width of the sheet, including Monday through Sunday data.

My confusion with this type of formula is that is doesn't seem to reference the cells with the "+" signs themselves ... but it works! Trying to recreate it for Tuesday's data is confusing to me since I can't simply drag the data outliner from Monday to Tuesday referencing those cells instead.

Thanks so much for your help.
0
Saqib Husain, SyedEngineerCommented:
Here is a modified formula

=TEXT(INDEX($A$9:$A$60,MATCH("+",OFFSET($C$8,1,MATCH(Q16,$C$3:$H$3,0)-1,52),0)),"[$-409]h:mm AM/PM;@")&" to "&TEXT(INDEX($B$9:$B$60,MATCH("+",OFFSET($C$8,1,MATCH(Q16,$C$3:$H$3,0)-1,52))),"[$-409]h:mm AM/PM;@")


The $C$8 is now directly connected to the first column of a day. You can change the two occurences of $C$8 to $J$8 for Tuesday and so on
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Instead you may use a single formula for all the days and names.

=IFERROR(TEXT(INDEX(Sheet1!$A$9:$A$60,MATCH("+",INDEX(Sheet1!$A$9:$P$60,,MATCH(B$1,Sheet1!$1:$1,0)+MATCH($A2,Sheet1!$D$3:$I$3,0)-1),0)),"h:mm AM/PM")&" to "&TEXT(LOOKUP(2,1/(INDEX(Sheet1!$A$9:$P$60,,MATCH(B$1,Sheet1!$1:$1,0)+MATCH($A2,Sheet1!$D$3:$I$3,0)-1)="+"),Sheet1!$B$9:$B$60),"h:mm AM/PM"),"")

Which is copied across and down.

So no need to change formula for each day.

Please refer to the attached workbook and on Summary Sheet, you will find the formula in B2 which is copied across and down.
Schedule-EE-2.xlsx
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Just forgot to tell you that in D1 (Monday), you had one extra space in the end and I deleted that extra space to make the formula work properly.

If you try the suggested formula in your workbook with that extra space, the formula will return error for Monday, so make sure all your day headers in sheet1 don't contain any leading or trailing spaces.

Also though I have created another sheet named Summary just because your data on Sheet1 will expand across the columns to have other day's data but if you wish you can try the same formula on sheet1also by just changing the referenced cells $A2 and B$1 in the formula in B2 of Summary Sheet.

Hope this helps.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Cactus,

Did the formula suggested work for you?

sktneer
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.