10-28-2020 04:15 AM
10-28-2020 04:15 AM
10-28-2020 05:00 AM
Fill to the right to column F, then down to row 50 (or vice versa).
Fill down to row 50.
10-28-2020 08:39 AM
@Hans Vogelaar Thanks for this! It worked again.
Just to add complexity -- I would like to add a 'check' by adding a Total (Row 52) vs =sum function. Filtering the data, I can trust Row 52 -- but now, the 53, 54 and 55 are not following.
10-28-2020 01:14 PM
@Hans Vogelaar Hi Hans, sorry I wasn't clear at all.
Basically, rows 53 and 54 in total should equal row 52, with a formula that would match column A and column C, row 1, row 6, etc.
Something like, if 'CC_ABCD - Apples' & 'All other OPEX' & '2018.Q1' matches data in Search tab in Column S, then return value. Sorry, I'm not good at this.
10-28-2020 01:54 PM
There is no reason rows 53 and 54 should add up to row 52.
The formulas in row 53 and 54 sum all values on the Search sheet for Apples and Bananas, but those rows have both IHQInternational ManagementHR / Staff Costs and IHQFruitsAll other OPEX in column R.
The formulas in row 52 sum the values for IHQFruitsAll other OPEX only, not those for IHQInternational ManagementHR / Staff Costs
10-28-2020 02:03 PM
So, rows 53, 54 (and 55 if with values), should add up to row 52, in case Column R in Search tab says IHQFruitsAll other OPEX. So the qualifier will be IHQFruitsAll other OPEX.
If I filer Column R in Search tab, the totals in Column E in Search tab would be 547.9 (divided between CC_43598 - Bananas and CC_ABCD - Apples.)
10-28-2020 02:07 PM
@Hans Vogelaar I promise, I'm doing my best to express my thoughts :-).
If A53 in Return Value matches Column A in Search, C1 in Return Value matches Column R in Search, C6 in Return Value matches Row 2 in Search, then return value in Search tab range B4: Q38
10-28-2020 02:10 PM
Fill to the right and down.
10-29-2020 09:40 AM
I am attempting to fill cells C10:M12. Qualifiers are: Rows 1,2,3 and Column A -- all in Sheet3. Sheet4 has all the data.
I tried a combination of the formulas you gave me so far, but it doesnt seem applicable on this one since I added a new 'qualifier' which is Row 2.
10-29-2020 10:20 AM
10-29-2020 12:08 PM
This time, I literally copy/pasted the formula onto my test sheet but the answer is not coming up correctly.
I was expecting Row 10,11,12 to add up to Row 9. What am I doing wrong?
10-29-2020 12:17 PM
It would seem, that somehow, the values are picking up the next column other than intended: I really don't understand how come.
10-29-2020 02:21 PMSolution
Sorry, my mistake. It should have been