Matching two sets of data to return a value.

[prcd6x]

Hello everyone, This is my first post, so bear with me.

I need to reference the text in A4 and the date range in B4 and return the data for that corresponding text from U:U into G7 (and H7, I7, O7, P7, Q7, H14...etc).

The solution can be a formula or VBA whatever is easitest. The data in S4:W30 or Y4:AC40 (whichever format is easiest to work with) is in another workbook, just condensed here for ease. This (S4:AC40) is variable data, so it will change every time the supporting reports are run. So that has to be accounted for.

The dates in R35:W40 are also on a different sheet, but here for ease.

I left some formulas that I have tried, to no avail in G7, H7, G14, H14, G21.

Thanks for any help! Let me know if you need any other info.

 

[SirJB7]

Hi, prcd6x!

Don't care much about presentation putting all together in the same worksheet for clearness, sometimes -as in this case- it's better to have all data sources separated.

Between the 2 formats in S4:W30 or Y4:AC40, a priori I rather prefer the 2nd one as it has only data lines without total lines.

Apart from this I have these doubts:

a) G7: =SUMA(SI(S38=T7:T10;U7:U9;0)+SI(T38=T7:T10;U7:U9;0)) -----> in english: =SUM(IF(S38=T7:T10,U7:U9,0)+IF(T38=T7:T10,U7:U9,0))
shouldn't it be the same ranges in column T than in column U? (either from rows 7:9 or 7:10)

b) GRD in calendars is GA in table? SR is SA too? Obviously I guess that OTH is OTH

c) Why are both sample ranges of different size and contents? Do they represent the same data or not?

d) G7 vs. G14 vs. G21 formulas:
Why do they use S38, A11 & S38?

e) Which is the relation between dates in the 6 (3x2) calendars and the week table at S37:S40?

Would you please elaborate?

Regards!

 

[prcd6x]

SirJB7,

I will do my best to answer your questions.

a) The range should be the same in column T and U, 7:10

b) Correct

c) The data is the same information, but it is "live data". So every time the data it is pulled it will vary. The first set (S4:W30) was pulled yesterday, then I pulled a new set today, and tweaked the output a little to see if it was easier to fit into this project.

d) These were just me trying different things to see if it would work. Realistically (I think) it needs to be A(4 or 11 or 18) designating the BN (column A and column S or Z) and then the date range B(4 or 11 or 18) or S38:W38; when those two match up, return the values in U, V, or W.

e) The (3x2) calendars is the finish product and displays the date range of the work week. S37:W40 are the actual work days. Either sets of data can be used.

I hope this helps. I apologize for the confusing layout. I tried to condense it down, because all of the workbooks combined seemed like a lot of data that would be (confusing).