Formula to search by sheet name and by table name using vlookup and indirect function?

[Lacan]

Hi guys,

Would like to search value using vlookup and indirect function through by sheet name and table name?
Plus, in every sheet has different table names in different arrays.
Can you help me?
Thanks guys.

 

[blanchdm]

You need to provide way more information if you want useful assistance. Yes, you can search values using vlookup, but I literally have no idea what you actually need help with.

 

[Lacan]

Thanks Blanchdm

Let me clarify


1. Have different sheets names like: October, November and December.

2. In each sheet there is 4 different tables with my schedule week like: week one, week two, week three and week four.

3. Each table are in different arrays like: table "week one" => B3:L6 ; table "week two" B19:L32 ; table "week three" U3:AE16 ; table "week four" U19:AE32.

4. Want I pretend is from the main sheet lookup my appointments using sheet name and the table name.

5. Example of a table:

Week 1
B3
Day Monday | Tuesday Wednesday Thursday Friday
Hour
09:00 APPLE |
10:00 SINGER |
11:00 PHILLIPS |
12:00 |
13:00 LG |
14:00 CREATIVE |
15:00 DOOR |
16:00 SUND |
17:00 SKUNK |
18:00 |
19:00 |

6. Example of the main sheet:


MONTH | WEEK


Day Monday | Tuesday Wednesday Thursday Friday
Hour
09:00 |
10:00 |
11:00
12:00 |
13:00 |
14:00 |
15:00 |
16:00 |
17:00 |
18:00 |
19:00 |


Can anyone give useful advice?

Thanks bros.

 

[blanchdm]

All right, now I have something to work with!

For starters, if you want to use table names, you have to understand that each name must be unique. That is, you can't name a table "Week1" in the October tab and also name a table "Week1" in the November tab. Instead, each table needs its own unique name, like "OctoberWeek1" and "DecemberWeek3", etc. Then say your Main Page is set up something like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 89"]Select Month[/TD]
[TD="width: 84"]Select Week[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Monday[/TD]
[TD="width: 64"]Tuesday[/TD]
[TD="width: 80"]Wednesday[/TD]
[TD="width: 64"]Thursday[/TD]
[TD="width: 64"]Friday[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD]Week1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9:00[/TD]
[TD]Apple[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10:00[/TD]
[TD]Singer[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11:00[/TD]
[TD]Phillips[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13:00[/TD]
[TD]LG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14:00[/TD]
[TD]Creative[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[TD]Door[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16:00[/TD]
[TD]Sund[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17:00[/TD]
[TD]Skunk[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Let's say that Monday at 9:00 (Apple) is in cell F2. Use this formula:

F2=INDEX(INDIRECT($A$2&$B$2),MATCH($E2,$E$1:$E$12,0),MATCH(F$1,$E$1:$J$1,0))

You can drag the formula both down and across to fill the table, and that should give you what you're asking for.


For the record, this might not be the greatest system in the first place, since most months are not nicely organized into four sets of weeks. But if the system works for you, that's what matters!

 

[Lacan]

Thanks Blanchdm.

Great advice man!!!!

Helped a lot!!!

Also tried and worked [=VLOOKUP($E2;INDIRECT($A$2&$B$2));2;0)]

Then changed de column number.

You said "For the record, this might not be the greatest system in the first place, since most months are not nicely organized into four sets of weeks."

Would like to learn more about excel. Just curious in your opinion what system should be used?

Thanks Bro.

 

[blanchdm]

Glad you got things working to your satisfaction!

All I meant by my comment is that 4 weeks is not the same as 1 month (except for February, 3/4 of the time). Thus, you might be better off organizing your schedule by actual dates. If you still need to know the days of the week, Excel can help you there, too!

Say column A is filled with dates (10/12/2016, Oct-12-2016, whatever; formatting is irrelevant as long as Excel knows what year it is). In column B, this simple formula:

[quote
=Text(A1,"dddd")
[/quote]

Gives you the day of the week.

 

[Lacan]

Thanks again Blanchdm.
Hope to hear from you soon.