Index & Match with the name of Tab

[ypurcaro]

Hello,
I hope all is well. I don't know if we can use the index and match in this case. I tried, but I failed because I don't know how do I point the code to the name of the tab. I hope I could get some help on this one. Thank you in advance.

Table I need to fill

Date	Total Hour	Location
01/02/2022	120	West
02/02/2022	135	West
03/01/2022	150	West
04/01/2022	110	West

This is the table I have all information, we call it score card. The spreadsheet has 4 tabs - East, West, North & South. The table is like below, it's more like a horizontal table. What I need to do is once I entered the hour to this table, it will also fill out the above table. Remember, this is on the tab named "West".

Date	01/02/2022	02/02/2022	03/01/2022	04/01/2022	05/01/2022
Total Hour	120	135	151	110
Vacation Hour	50	45	35	25
Regular Hour	110	90	75	80

 

[Gerald Higgins]

Should the number for 03/01/2022 be 150, as per the top table, or 151, as per the bottom table ?

 

[ypurcaro]

Should the number for 03/01/2022 be 150, as per the top table, or 151, as per the bottom table ?

Sorry, it should be 151. Good catch!

 

[Gerald Higgins]

OK, let's make some guesses about how your data is laid out.
With the sample data from post #1, let's assume the first table is in the range A1:C5.
And the second table is on a sheet called "West", in the range A1:F4.
The "East" / "North" etc sheets all have an identical layout to the "West" sheet.

This formula in B2 in your first table will look at the Location in col C, and the date in col A, go to the relevant sheet, and return the Total Hours.

=OFFSET(INDIRECT(C2&"!A1"),1,MATCH(Sheet1!A2,INDIRECT(C2&"!B1:F1"),0),1,1)

Note, there may be better ways of doing this, but this does seem to work.

 

[ypurcaro]

OK, let's make some guesses about how your data is laid out.
With the sample data from post #1, let's assume the first table is in the range A1:C5.
And the second table is on a sheet called "West", in the range A1:F4.
The "East" / "North" etc sheets all have an identical layout to the "West" sheet.

This formula in B2 in your first table will look at the Location in col C, and the date in col A, go to the relevant sheet, and return the Total Hours.

=OFFSET(INDIRECT(C2&"!A1"),1,MATCH(Sheet1!A2,INDIRECT(C2&"!B1:F1"),0),1,1)

Note, there may be better ways of doing this, but this does seem to work.

I tried to use the code you provided and I don't understand the C2 after the indirect. Here is what I have as attached. I it gave me an error message. Thanks for your help.

 

[Gerald Higgins]

That formula you've used in the right hand sheet in post #5 looks quite different to the one I suggested.
For example, it looks like you haven't used C2 in your formula, you've used C9.

And the layout of the left hand sheet in post #5 is completely different to the layout of the second table in post #1.

 

[ypurcaro]

That formula you've used in the right hand sheet in post #5 looks quite different to the one I suggested.
For example, it looks like you haven't used C2 in your formula, you've used C9.

And the layout of the left hand sheet in post #5 is completely different to the layout of the second table in post #1.

Yes, that's the actual layout. So, do I have to clean it up and re-organize the data, I mean to fill every single cell? Thank you.

 

[Gerald Higgins]

Yes, that's the actual layout

What is the actual layout ?
The layout in post #1, or the layout in post #5 ?
Just be clear about what your actual layout is please, and we can then adapt the formula to match.

 

[ypurcaro]

What is the actual layout ?
The layout in post #1, or the layout in post #5 ?
Just be clear about what your actual layout is please, and we can then adapt the formula to match.

Sorry, the actual layout is post#5. I thought the layout is not important.

 

[Gerald Higgins]

Hi - you can have whatever layout you like.

But if you present one layout, and then we suggest a solution for that layout, and then you apply that solution to a different layout, then it's possible it won't work, unless you adapt the solution to take the new layout into account.

I'll take a look at the new layout shortly.