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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.