One workbook, multiple sheets, referencing various cells to display

Hi All,

I think my request might be beyond Excel. If it's not possible, no problem, i've tried to figure it out, by i'm struggling.

Basically, i'm trying to reference a schedule, and have multiple cells referenced and display on other sheet in the workbook.
I have a workbook with 6 sheets. 3 Sheets for each week, as the schedule is done on a bi-weekly basis.

I'm trying to figure out how to get one cell to search for it's value on another sheet, if it finds it, then give the value of another cell in the same row.

I've noticed that examples are usually requested, so i made a sample workbook. If you take a look, you might notice some sheets have multiple pages, that's for each day of the week,

Any help at all is appreciated.

Welcome to the forum!

This will be easy to achieve with a few tweaks to your data layout and headings.

For example, on the START sheet, please list ALL workers in one table, not two spread across the page.

Also, standardised headings (e.g. Sun in both places or Sunday, but not a mix of the two).

Once these things are done, INDEX MATCH will get you there.

Oh, and don't use merged cells - they are the spawn of the devil.

Sounds good! You were a big help. One last issue that i'm running into though, In my example, I have two people down for U2. Is it possible to have the 1st name returned on one line, and another formula to return the 2nd name?

Yes, it's possible, but I suggest you provide a fresh workbook.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

EDIT: Fixed my 0 issue. Now i just need to deal with the duplicate names.

Will take a look in the morning.

Try this:

=IFERROR(INDEX(Week1!B$1:B$13,AGGREGATE(15,6,ROW($2:$13)/(Week1!C$2:C$13=$A4),ROW(1:1))),"")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

It works here.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
1	DATE	Sunday, April 21, 2019
2
3	Day Shift			Evening Shift			Night Shift
4	U1	Noah		U2	William		U3	Michael
5	U1	Liam		U2	Ethan		U3	Benjamin

Sheet: Shift List Start

I'll try!

=IFERROR(INDEX(Week1!B$1:B$13,AGGREGATE(15,6,ROW($2:$13)/(Week1!C$2:C$13=$A4),ROW(1:1))),"")

Week1!B$1:B$13 needs to be the range you are drawing the data from (names) - it has to start with row 1.

$2:$13 needs cover the number range that is the same as the lookup range.

Week1!C$2:C$13 is the range containing U1, U2, etc. (lookup range).

Please do not PM members asking for help. If you need further help with this, post here. Thanks.

The problem is that the real workbook is significantly different to the sample you provided. A different approach will be required. We didn't know that the match items on the left (U1, etc.) would not all be the same.

I will see what I can do, but you really should have provided a more realistic sample of data in the first place!

Which version of Excel do you have? Office Home tells me nothing useful.

It looks like it, Jason - well done!

Yes, it's a fine line between simplified and over-simplified! You are forgiven.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Yes. That is correct.

Could you please update your forum profile with the correct version of Office that you are using?

Thanks for updating the profile.

See if the following will help:

Formula:

Please Login or Register  to view this content.

Note that the highlighted portions of the formula will need to be changed for each day. For example starting with April 8th the formula will need to read:

Formula:

Please Login or Register  to view this content.

Note also that the dates in B1, B38 etc have been changed from text to actual dates formatted dddd, mmmm dd, yyyy In order to display cells B1:D1 were selected, then cell formatting (Ctrl + 1 > Alignment tab) 'display across selection' was applied (not merge and center).
Let us know if you have any questions.
Edit: I failed to notice that this thread had two pages and thought that the last post was #15. Please ignore this post if the information contained confuses things.