index,match question

in an older post i found an index/match solution that worked great for me. (thanks tim miller http://www.excelforum.com/l/763193-asdf.html) my question is: is there a way quickly change from say mon to tue to wed etc, without making 7 different break schedules? and thanks for any help.

southward, great! how did you get it to pull the data?

In Column R, S & T there is a helper table to help the VLOOKUP's in the formula's to find the correct columns of the Weekly Input sheet.

The VLOOKUPS are nested. One VLOOKUP is inside the other. The inside VLOOKUP provides the column_index_number for the outside one. It gets the number from the helper table.

The helper table provides the Lookup list (Column R) for Cell B1. Columns S & T provide the column index numbers to tell the VLOOKUP where to look in the Weekly Input sheet.

Hope all that made sense. LOL

I am sure there are a few Guru's around here that can provide a formula without a helper table.
I felt that this was the easiest and most expedient way.

Just keep the helper table in mind when you need to make modifications.
Give a shout if you need more help with this.

Cheers.

southward, again thanks for helping, but how did you get the dropdown list to work? when I try
to do the list I get a error. what I did was go to data validation, then list, and in the ranges i went to the schedule and clicked on the sun, mon tue etc cells, that's when the error came up. help please. I've tried to attach the file, but it's not working for some reason.

Bugdout,

You are going to have to more explicit in your explanation of the steps you are taking.

What sheet are you on? What cells are you selecting?

Here is a link that helps explain how to create a pull down list.

http://www.online-tech-tips.com/ms-o...ropdown-lists/

southward, I got it figured out, thanks for the great update to the sheet. I might need some help a little later, wife doesn't let me work at home....

Sounds good. Glad I could be of some assistance.

southward, i got everything in place for what I want it to do, and it works great for just Sunday??? If I change the day it starts to put in people who do not work.. any ideas? I've attached the file, if you could take a look at it and tell me where I'm messing up, I can fix it them.

bugdout,
I think I see what the problem is.
I'll work on it and get back with you.
Can't work on it right now.
Hope to have something for you by noon tomorrow.

Cheers

sounds good, what do you think it is? wierd it works for sunday, then puts in on off days if I change the day. and thanks again.

bugdout,

The problem was inattention to detail. I was so focused on getting the In/Out times to work that I completely forgot about the need for the names to change based on the daily rank.
That has been corrected in the attached file.
Take it for a spin and let me know if it needs more tweaking.

Cheers

southward, i put everything in place and it's almost perfect. ifigured out the problem with the cashiers for satuyrday, I didn't finish the ranking code. but on tue, wed and thur, for connie in textile, it shows her name, but not the schedule. I've tried redoing the formulas again, and they look right, but it doesn't put her on the break schedule right. any ideas?

bugdout,

I cleaned up all the formulas and arranged the overall table format in the hopes of making it more functional.

First and foremost, I added a Column A to both that represents the "Job". (i.e. "Manager", "Cashier", "Seasonal", etc...)
Each of these are setup with Named Ranges under the Name Manager.
Almost all of the formulas in the "Break Sced 1" sheet and the formulas that manage the ranking reference a Named Range in order to establish an array to index the correct information from the "Schedule 1" sheet.
Each Named Ranged is formulated to identify the correct array needed to determine how to rank the employees within their Job and correctly populate the "Break Sced 1" sheet.
Remember, if you want to change Job names, you also need to make similar changes to the Named Range via the Name Manager.

On the "Schedule 1" sheet there is a button "Sort Jobs" that can be used to keep all the Job titles in the same area. It will sort them in a specific order.

This will allow you to add or delete job position, or even change a person from one position to another.
For example: if you promote "Cashier" "Melody" to "Manager", just change Job to "Manager" and hit the Sort Job button to move "Melody" up to "Manager". This will also automatically update the "Break Sced 1" sheet.

Hope that makes sense.

I also added some Conditional Formatting to highlight different Job areas. You can use this to visibly separate the different job areas.

Take the new version for a spin and let me know if you need more adjustments.

southward, wow that is impressive. the folks i'm making this for will not be able to handle that. I'm attaching an updated version, but I still have the problem with hardlines on tue, wed thur, and the same problem with volunteers everyday. either someone is scheduled and no times are put on the break schedule, or they just don't show up. I've checked the formulas several times and can't find the problem. any ideas? can you check it out and let me know the issue. and I sure appriciate your help on this.

One of the problems with the hardlines is the second half of the formula in A52.

Please Login or Register  to view this content.

In all the formula in Column A the two half's for formula have to pretty much be the same.

The mistake here is the "MATCH(ROWS(A$52:A52)" in the first half is different from the "MATCH(ROWS(A$49:A49)" in the second half.

The key to this part of the formula is that it is directly relevant to the cell that it is in.
It creates the number that is matched to the ranking for that section on "Schedule 1".

The second problem I discovered is with someone like "Connie". The name is in there several places. The VLOOKUP is finding the first use of the name and returning that time information. In some cases the time info for that day is blank.
I'll see what can be done about this.
The Named Ranges I created for the other version isn't effected by this situation. I'll try and incorporate it, will keeping it user friendly.

southward, i noticed that 52/49 and fixed that. your right, the names throu it off. i changed the names and it works.. if I have say 2 steves, if i put in the first initial of the last name, would that work? and i can't say enough, thanks for helping.

bugdout,

Yes, adding a last initial would make the two entries distinct, but what happens if you actually have two or more Steves and one of them is assigned to several different jobs?

No worries. I have incorporated the Named Range formulas into your version of the spreadsheet. Without any bells and whistles.
I just added a helper column. Is a new Column A. It is currently hidden.
As long as the overall structure doesn't change, there shouldn't be any issues. Your current structure has the same limitations.

See the attached file. It doesn't look any different than the last one you sent me. Just better at handling duplicate names.

southward, very cool. I have one last issue. I'm trying to make 4 more copies of the same thing(for 4 other schedules) and everytime I try to copy the formulas over, thye don't work? it pulls down my network menu like I'm going to open or save it???? I've even tried just typing the formulas, and they just show blank results. any ideas?

bugdout,

Sorry, I didn't think to take into consideration more parameters associated with multiple schedule sheets.
The current named ranges that allow most of the formulas to work correctly are associated with one specific sheet.

I can fix this. Will take a little time.

You want 5 schedule sheets total? With associated Break Sced sheets?

southward, correct. I have the 5 schedules and 5 break schedules. it's just changing the 1 to 2 in the formulas doesn't work. anyway around this?

bugdout,

All fixed. The complication that arises from copying a sheet like this is that it also makes copies of the associated Named Ranges. Those copies are coded for the first original sheets.
A quick adjustment to the new Named Ranges and to the new sheets corrects association.

See attached.

Let me know if you need more tweaking

southward, thanks for responding, I opened the file, but nothing populates? been working on the kid cave the last couple days, sorry i didn't get back to you sooner.

bugdout,

The statement "nothing populates" can mean all kinds of things.
Please be more specific.
I have gone all over that spreadsheet to make sure all the Named Ranges and formulas correctly associated.

Kid's caves are cool

Hey hi there I am having problem in excel as I want to develop quotation system where you type lets say Australia in B9 (then it should match Australia in different sheet ) after that if I type Brisbane in C9 (then its should locate Australia against Brisbane row wise) then also there will be multiple(more than 10) shipping lines vertically but I want that if I write 40 feet then it should locate from the first column 40 feet (there will be 5 columns showing 40 feet 60 feet 80 feet and 100 feet) and wherever it intersect then it should compare that from all shipping line companies from Australia to Brisbane that too in 40 feet container which is cheapest one and then it should pop up somewhere in quote. would you be able help with this? I tried index and match together but match doesn't go further if there are multiple matches so very confused how to do it.

@ gauravsukhadia, What your asking sounds feasible. The way you are going about asking is wrong. You should NOT jump into the middle of someone else's thread to make your own request.

You should start your own thread with your request.
I suggest thread title of "Index match multiple criteria".
You should also provide a sample of your file. It will make it much easier to help find a solution.
I will watch for your new thread.

hi southward, back to work. when I open the file I get: this workbook contains noe or more links that can not be updated. then it asks to update all links, or attempt to update all values again. when i click on edit links i get error: source not found, so nothing populates. any ideas?
and thanks for helping.
as far as the kid cave goes: just have to wait for the lectricain to tie the sub panel to the main. yeah...argghhhhh spoiled kid.

bugdout,

I am not able to recreate the link error on my end. I found a stray link, but isn't giving an error.
The link is pointing to "Auto Calculate Schedule-New.xlsm" workbook and is looking for it in my downloads directory.

You may have moved or renamed your version of this file. That could cause a link error.

A kid cave with electricity! lol
Yep, spoiled!

I found were the links are. They are part of the Conditional Formatting.
Not in all of them thankfully. But enough to be a pain.

hey southward, yeah the 7yr old is spoiled, he wants to be a youtuber! I just want to watch a movie in quiet. if the conditional formating is removed, would the formulas work?

They should work if you repair the links via the Conditional Formatting Rules Manager.
From my end, I am not able to see where they are looking for the file, other than the file name.
Is the "Auto Calculate Schedule-New.xlsm" workbook in the same directory?

The solution could be as easy as putting the file where it is expecting to find it.

They should work if you repair the links via the Conditional Formatting Rules Manager.
From my end, I am not able to see where they are looking for the file, other than the file name.
Is the "Auto Calculate Schedule-New.xlsm" workbook in the same directory?

The solution could be as easy as putting the file where it is expecting to find it.

by directory, do you mean same area? yes, they are both on the desktop. Also, looking in the conditional rules area, the sheet: Schedule 2 doesn't show? How do I get it there?

I would think if they are both on the desktop, the one can find the other.
As for conditional rules area, I don't know how to direct you effectively.

Attached are some slides that I threw together to try and point you in the areas where I see the formula's that I think are causing the broken links.

southward, I've attached the complete file. there are no links or connections.. I tried to place the formulas into this file and could not get it to work. can you take a look? it is the file without the new formulas.

That explains a lot. The original file you sent me was missing sheets. Then you copied what I provide into your main workbook, less the Named Ranges which made everything work.
When you delete sheets or copy sheets to another workbook it creates links to that book.
Then you copied revised sheets back into to the master book. Yep, created a bit of a mess.

Working on cleaning it up.

OK. All cleaned up.
If there is more to the project, add them to this version of the file.
Please don't copy sheets from this version to other workbooks. Otherwise the Named Ranges that are built into this workbook won't copy over with a worksheet.

Let me know if you have any more issues with it.

Cheers

southward, thanks for helping, but when I try to open it I get a message saying one or more links can not be updated.
whne I continue I get the following message:
'C:\Users\Glenn|Downloads\AutoCalculate Schedule-New.xlsm could not be found.
Then if I continue the break schedules do not populate. Any Ideas?

southward, I attached the file with all layout and formulas formulas typed in the way you had them (I couldn't get it to populate since it was linked to a directory I never saw - previous post). I still can not get the break schedules to populate. there are no connections or links again in this one. can you take a look and let me know why it's not populating?

Hi bugdout,

Not sure how it happened. All the Named Ranges that are the magic behind the curtain are missing.
Attached are two files.

A spreadsheet with all the Named Ranges.
A pdf on how to re-create all the missing Named Ranges.

Follow the directions and let me know what happens.

I'll keep my toes crossed and hope it works for you.

southward, thanks for helping. i got the named ranges in and it still doesn't populate. there is still no connections or links (thank god). any ideas? I've spot checked formulas and don't see anything wrong (but i'm not an expert). could you take a look and see if i've done something wrong?

bugdout, I am very sorry. In my haste to get you an answer, I neglected to thoroughly evaluate that process.
At first look I thought that would be the easy fix.

I have manually copied all the Named Ranges over.
The Named Ranges are referenced by almost all of the formulas and help speed up the processing.
The Named Ranges are part of the workbook, not necessarily part of the sheets.
If sections of the sheets or formulas are copied over to another workbook, the Named Ranges won't copy over with them.

I also noticed blank areas of the hidden Column A.
It appears that rows were added without updating the cells in Column A.
They have to be updated just as the formulas in the ranking columns (AB through AH) have to be updated if you add rows.
The "job/position title" names that populate Column A are what help the Named Ranges establish reference arrays for the other formulas to work.
I have highlighted them so that you can see how they relate to their specific areas.
You can hide Column A. Just don't forget to update it if you add more rows.

I have been researching to figure out how to create Dynamic Named Ranges between two named areas, so we can do away with Column A.
Will let you know if I figure out a way to make it work.

Cheers

i get it. does the break schedules populate on your end? also, the columns in the ab thru ah - how do you get them to show what is in the cells besides clicking on each cell? can you do that with any cell? could come in handy in the future...

Yes, break schedules are populating on my end and columns AB through AH show the ranking of each filled position for each day of the week. Not yours?

See attached screen shots in the pdf. Yours doesn't look like it?

thats odd, when i open your file i can see the formulas if i don't enable the documant, but when i enable it, they don't show until i click on a cell. what would cause that? if you email the file would that prevent that from happening?

I am sorry. I am at a loss for what might cause that. It could be an incompatibility with 2007.

Maybe someone else on the forum could take a look at the formulas to determine if it is a difference between 2007 and 2010.

hi southward, can you put a copy of the spreadsheet here without any links/connections? maybe that would help me out. seems like no one can figure out why my cells don't show results. wierd

also, what is the eval? i guess 2010 doesn't have that?.?

Hi bugdout,

I am so sorry for the headache this has been for you.

Here are a couple of links that may help explain "eval".

https://support.office.com/en-US/art...8-5290DA4D4166

https://www.extendoffice.com/documen...to-formula.htm

For your spreadsheet "eval" is used to turn the string referenced from a Column A cell into an array for the AB through AH columns formulas.

I am not sure if "eval" is the problem or not. I have read that "eval" is part of a "MoreFunc" add-in I have installed.
The previous file that I uploaded was clean of any links/connections.

For the attached version of your spreadsheet I have inserted a UDF (User Defined Function) called "Eval".

Please Login or Register  to view this content.

Hopefully the UDF will make the "eval" function within the formulas work properly on your version of Excel.

Again, sorry for the headache.

southward, no headache, thanks for helping. I just don't know why it works for you and not me. ha ha, just my luck. I read the first link (it showed for access?). the second link got me a 404 not found. where did you put the UDF? and again, really thanks for helping.

What version of excel are you using again? 2007?

bugdout, don't know why the UDF (which is in Module 1 of the VBA) isn't working like it should.

I have removed all the "eval" and replaced them directly with watch was being referenced to.

This should work for you know.

Let me know.

Cheers


p.s. I am using 2010.

hey southward, what ever you did, did the trick. thanks a ton for helping on this. now i'm gonna figure out how to add rows and have the formulas automatically add. again, thnaks for helping.

bugdout, glad this one works for you. Give a shout if you have trouble expanding it.

Cheers