Best way to reference table in formulas and how to effectively copy it across.

What's the best way to reference tables in formulas? I'm attaching a file with a calendar type layout. What I want is the formula in B4 in the desired results sheet to tally up the number of ML for that week. I can get it working no problem with Countifs for the ML bit - it's referencing the date in the columns that I can't get to work...ideally then how to copy across that for the other weeks. Thanks.
EE_Calculations_WTE.xlsx
LVL 1
agwalshAsked:
Who is Participating?
 
Roy CoxGroup Finance ManagerCommented:
I think you want something like this article explains

Absolute Structured References in Excel Table Formulas

There is also an addin to help you create the formulas

Addin
0
 
Rob HensonFinance AnalystCommented:
Try this in Results!B4 and copy across and down:

=COUNTIF(OFFSET(Table3[[#Headers],[01/01/2018]],0,MATCH(B$3,Table3[#Headers],0)-1,COUNTA(Table3[[#All],[01/01/2018]]),1),$A4)

The hard references to 01/01/2018 is only for identifying the top left of the table and then counting that column for number of rows.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What about this?
In B4
=SUMPRODUCT((Table3=$A4)*(Table3[#Headers]=B$3))

Open in new window

and then copy across and down.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
To be honest though, I would use a different format for the data.

I assume there is a field in the table which isn't shown in your sample giving each row of data some form of ID; eg Employee ID.

I would use a table in this configuration:

ID                  Date                  Value
Record 1            01/01/2018            1.00
Record 2            01/01/2018            1.00

You can then use various summary methods on that table; COUNTIF(S), SUMIF(S), Pivot Table.

See attached.
EE_Calculations_WTE.xlsx
0
 
agwalshAuthor Commented:
@Rob Henson - normally I would totally agree with you and I did think about that. However, the situation is that these are very non-techie users. They simply would not "get" the idea of entering data that way and that's OK. They've seen this particular model in action and they liked it.  I'm setting it up so that the only thing they have to do is enter a number 1.0 for a full day or SL for sick leave or whatever the type of absenteeism is.  Even that amount they will need training on. (Not exaggerating). Everything else to be done via password protected formulas.
0
 
agwalshAuthor Commented:
@Roy Cox - *that* was exactly what I was looking for...just couldn't remember where I had seen it...solved the problem! Thank you!
0
 
Rob HensonFinance AnalystCommented:
Users would literally just have to add the data to the bottom of the list.

I would think that adding data to the bottom of a list is simpler than having to find the right place in a table.

With the list setup it doesn't matter on sort order of the data, the summarising deals with that.
0
 
Roy CoxGroup Finance ManagerCommented:
Rob

The issue was to drag formulas across columns so that they adjust automatically. Standard Absolute and Relative references using $ don't work in Tables.
0
 
Rob HensonFinance AnalystCommented:
Roy, I understand that; with the different format of source data this wouldn't be an issue as the reference to the Table column would stay static, ie Date column, rather than having to find the correct Date column.
0
 
Rob HensonFinance AnalystCommented:
Roy's suggestion was confirmed as being what the author was looking for
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.