nested formula?

[AFITT]

Hi;

I am trying to create a formula that will combine two separate criteria from one sheet and apply the value from a certain cell. I have attached a file to help better understand.

[JohnTopley]

Put your data on Sheet2 so we know which rows/columns to extract from and re-post the file.

[angela.fitt]

I have re posted the attachement as requested. Thank you

[JohnTopley]

Query: "Cash from Sale" Count or Collected?

And no other data to be extracted?

[angela.fitt]

Each of the headings on sheet 1 has data to be extracted from sheet 2 (exact column name) if it matches the cashiers name from sheet1 (Adelle) There are many cashiers so I am hoping to simply copy the formula down. Formulas need to be on sheet 1.

Hope that is not too confusing.

[bebo021999]

I guess there are more than 1 name, right? How others be arranged in sheet 2?

[angela.fitt]

I have reposted the attachement again. On sheet 3 you will see how the additional cashiers show on the report. On sheet one I have used Alina to show I would like the data to appear.

[JohnTopley]

..but for each heading there are 2 values listed under "Count" and "Collected"


See if attached does what is required.

I changed the format of Sheet2 to make it much easier to produce/copy down formula.

[angela.fitt]

Actually looking for Collected amounts only. There are many cashiers and the report is exported on a csv file so I would rather not have to transpose the data. I did attach another copy with a sheet 3 to show how it appear for multiple cashier.

[JohnTopley]

In C3 of Sheet3

=INDEX(OFFSET(Sheet3!$A$1,MATCH($A3,Sheet3!$A:$A,0)+MATCH(C$2,Sheet3!$B:$B,0)-3,3,1,1),0)

Copy across and down.

[bebo021999]

Try in C3 sheet1 then drag down and accross:

Please Login or Register  to view this content.

[angela.fitt]

It works for the first couple of rows but then starts to repeat figures when I get to the bottom. Can't figure out what it is referencing. I will keep trying. I have about 25 rows on names.

[JohnTopley]

I added 4 more names and it appears to work OK.

Are all formats (entries) identical i.e. same number of rows?

[AFITT]

It appears that those employees that have some of the payment methods missing throw a wrench into the overall formula. I have added an iferror to the beginning of the formulas string as not all employees work everyday. I have highlighted the incorrect info in red.

[JohnTopley]

The only solution I can offer is with "helper" columns in column H, I and J plus a "lookup" table in L & M

in H2

=IF(B2="","",IFERROR(VLOOKUP($B2,$L$1:$M$18,2,0),""))

in I2

=J2&H2

in J2

=IF(COUNTIF($A$2:$A2,$A2)=1,MAX($J$1:$J1)+1,IF(A2="",J1,""))

in Sheet "Mon_Test"

in C3

=IFERROR(INDEX(Sheet3!$D:$D,MATCH(VLOOKUP($A3,Sheet3!$A:$J,10,0)&VLOOKUP(C$2,Sheet3!$L$1:$M$18,2,0),Sheet3!$I:$I,0)),0)

Copy across and down

Table in L &M could be a named range so defined only once.