0

I am trying to obtain data from multiple worksheets:

Where: sheet 1 - has an F2 cell that enables you to choose from a drop down list of names and each of these names equals a different worksheet in the one workbook

sheet1!b2 is a cell that enables you to choose from a drop down list of colours that you will use to search the F2 cell selected worksheet

sheet1!h5 is a cell that meets a month criteria

F2 selected worksheet cells are: d4:d36 are the cells in the worksheet selected through f2 where I want to add the data that matches the colour in b2:b36 on the same worksheet

a1:aa1 are date fields on the f2 selected worksheet that meet a further sort field on the initial sheet 1 at h5.

Hope this makes sense.

I keep getting a #ref error but do not know why?

=SUMIFS(INDIRECT(F2&"!d4:d36"),INDIRECT(F2&"!b2:b36"),Sheet1!B2,INDIRECT(F2&"!a1:aa1"),Sheet1!H5)

Any help would be appreciated.

1 Answer 1

1

Try using Formulas|Evaluate Formula, which will allow you to step through the process. I suspect the value in F2 is incorrect and therefore trying to redirect you to a cell or cells that don't exist.

2
  • when i do this, it comes up with the correct worksheet but will not go beyond #ref! in cell.
    – Rob
    Jul 23, 2014 at 8:55
  • I just found it - worksheet had space in name! so my sumif works now just the sumifs issue but that can wait.
    – Rob
    Jul 23, 2014 at 9:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .