Quickfill formulas from cells not ajoined

[mahalek1976]

I'm creating a column of data straight down below a data entry field. I'm trying to quickfill by dragging down with the fill handle however, I want to be able to pull from cells that are not all combined in a single column or row. For example, in the attached file I want to select the handle of cell D685 and drag down filling in from the calendar field above the referenced cell. However, the formula in D685 references data from cells M91 & M92 (See formula below). The next cell down, D686 will pull data from A95 & A96, the cell down, D687 will pull data from C95 & C96, and so on and so forth. Is there a way to quickfill these formulas?

=IF(AND(ISNUMBER($M$91),$M$92=$BT$7),1,"")

[Craig K.]

In cell D681 put this formula in:
=IF(IFERROR(AND(HLOOKUP(DAY(A681),$A$91:$N$92,1,FALSE)>0,HLOOKUP(DAY(A681),$A$91:$N$92,2,FALSE)=$BT$7),FALSE),1,IF(IFERROR(AND(HLOOKUP(DAY(A681),$A$95:$N$96,1,FALSE)>0,HLOOKUP(DAY(A681),$A$95:$N$96,2,FALSE)=$BT$7),FALSE),1,IF(IFERROR(AND(HLOOKUP(DAY(A681),$A$99:$N$100,1,FALSE)>0,HLOOKUP(DAY(A681),$A$99:$N$100,2,FALSE)=$BT$7),FALSE),1,IF(IFERROR(AND(HLOOKUP(DAY(A681),$A$103:$N$104,1,FALSE)>0,HLOOKUP(DAY(A681),$A$103:$N$104,2,FALSE)=$BT$7),FALSE),1,IF(IFERROR(AND(HLOOKUP(DAY(A681),$A$107:$N$108,1,FALSE)>0,HLOOKUP(DAY(A681),$A$107:$N$108,2,FALSE)=$BT$7),FALSE),1,"")))))

An answer is easier to give when you upload a spreadsheet for us to play around in.

[mahalek1976]

I will gladly send it to your email but it is 3.84mb and too large to upload here. I tried.

[mahalek1976]

help2.jpg

hERE IS WHAT I AM GETTING

[Craig K.]

You're getting the nesting error because you are using excel 2003 whereas your user profile is showing 2010. Important information when trying to help. As for uploading a file, we (on the forum) just need a sample of what you're working with with any sensitive information removed.

That being said, try this instead:
Change all the numbers in your calendar (row 91, 95, 99, etc) to the actual dates and reformat the cells to custom and put "D" in the Type field. This will keep your aesthetic the same.
Now in cell D681 put this array formula in using Ctrl+Shift+Enter
=IF(INDIRECT(ADDRESS((MID(MAX(IF($A$91:$N$110=$A681,VALUE(900&TEXT(COLUMN($A$91:$N$110),"000")&ROW($A$91:$N$110)))),7,999))+1,MID(MAX(IF($A$91:$N$110=$A681,VALUE(900&TEXT(COLUMN($A$91:$N$110),"000")&ROW($A$91:$N$110)))),4,3)))=$BT$7,1,"")

If you don't use Ctrl+Shift+Enter the array formula will not work!

[mahalek1976]

Please check my original post. I was able to get a spreadsheet posted.

[Craig K.]

It looks like you completely edited your first post and are now asking a different question. This is not allowed and you should have just started a new thread.

[mahalek1976]

Sorry. i was finally able to get the spreadsheet added. I will create a new post.