Calendar Tracker Formula Help

K

kiyo052

Board Regular

Joined

Oct 11, 2013

Messages

134

• Nov 4, 2013

• #2

If it isn't possible please post saying it's not possible. This way I don't get my hopes up. Thanks to all those who know the solution to this problem!!!!!

 

Upvote 0

S

shyy

Well-known Member

Joined

Nov 6, 2008

Messages

1,484

• Nov 4, 2013

• #3

Would help if you post up a sample data instead, not everyone is keen on d/l. Do you mean the cells in each row to replicate the same information as another sheet?

 

Upvote 0

K

kiyo052

Board Regular

Joined

Oct 11, 2013

Messages

134

• Nov 4, 2013

• #4

I'm pretty much trying to get this sheet 1:

into sheet 2 without manually typing C2=Calendar!B3, C3=Calendar!B4, C4=Calendar!B5, so forth and so forth for all of the names, room, dates, and slots.

 

Upvote 0

M

markmzz

MrExcel MVP

Joined

May 7, 2011

Messages

3,699

• Nov 5, 2013

• #5

Maybe this:

In B3 - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code:

=IFERROR(INDEX(Tracker!$C$2:$D$170,
MATCH($A3&OFFSET($B$2,INT((ROWS(B$3:B3)-1)/8)*8,INT((COLUMNS($B3:B3)-1)/2)*2),Tracker!$A$2:$A$170&Tracker!$B$2:$B$170,0),
MOD(COLUMNS($B3:B3)-1,2)+1),"")&""

Markmzz

 

Upvote 0

M

markmzz

MrExcel MVP

Joined

May 7, 2011

Messages

3,699

• Nov 5, 2013

• #6

Another way:

Code:

In B3 - use only Enter to enter the formula

=IFERROR(LOOKUP(OFFSET($B$2,INT((ROWS(B$3:B3)-1)/8)*8,INT((COLUMNS($B3:B3)-1)/2)*2)&$A3,
Tracker!$B$2:$B$170&Tracker!$A$2:$A$170,
OFFSET(Tracker!$C$2:$C$170,,MOD(COLUMNS($B3:B3)-1,2))),"")&""

Markmzz

 

Upvote 0

K

kiyo052

Board Regular

Joined

Oct 11, 2013

Messages

134

• Nov 6, 2013

• #7

Do you mean in B3?

Also, I'm suppose to drag it through all 4 columns?

The idea is whatever is put into the Calendar tab it automatically populates into the tracker tab.

Please let me know. Thanks!!!!!

 

Upvote 0

M

markmzz

MrExcel MVP

Joined

May 7, 2011

Messages

3,699

• Nov 6, 2013

• #8

kiyo052 said:

Do you mean in B3?

Also, I'm suppose to drag it through all 4 columns?

The idea is whatever is put into the Calendar tab it automatically populates into the tracker tab.

Please let me know. Thanks!!!!!

Click to expand...

Sorry, but I didn't understand.

My formulas are for cell B3 of sheet1 (Calendar) and copy to the right and down.

Markmzz

 

Last edited: Nov 6, 2013

Upvote 0

K

kiyo052

Board Regular

Joined

Oct 11, 2013

Messages

134

• Nov 6, 2013

• #9

Sorry for the confusion!

I'm trying to go the other way. I'm going to be updating Sheet 1 (calendar) and I want Sheet 2 (tracker) to update.

I threw examples into that. The original idea is that this is a project where people would print out the calendar and I would be looking at the tracker for specific data and possibly future data.

I've updated the calendar a little bit

I don't need the 3 extra columns in the tracker, but I understand that the columns will effect the formula

Here is the tracker sheet so you don't have to go back up



Again apologizes for the confusion!!!!!!!!!

 

Upvote 0

M

markmzz

MrExcel MVP

Joined

May 7, 2011

Messages

3,699

• Nov 6, 2013

• #10

Hi,

How would be the calendar for October/2013?

Markmzz

 

Upvote 0