Is this possible?

[Brianas]

I apologize, I'm a rookie at some of this...

I'm struggling to find a way, outside of VB scripting, to have excel calculate a formula for me. I have a formula that pulls from a separate sheet, but I'd also like it to use the current sheet as well. For example:

=SUMPRODUCT(COUNTIF(Schedule!D143:D147,"=7:00 dir")+(COUNTIF(Schedule!D143:D147,"=8:00 DIR"))+(COUNTIF(Schedule!D143:D147,"=9:00 DIR")+(COUNTIF(Schedule!D143:D147,"=11:00 DIR"))+(COUNTIF(Schedule!D143:D147,"=Mail"))))

I'd love to have just the 143 portion of D143 and just the 147 portion of D147 taken from a single cell on the current page so that for the next column I can specify in a different cell 150 and 152 and have those two values adjust the formula to read.

=SUMPRODUCT(COUNTIF(Schedule!D150:D152,"=7:00 dir"....

[Bob Phillips]

COUNTIF(INDIRECT("Schedule!D"&A1&":D"&B1),"=7:00dir")

etc.

---
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Brianas" <[email protected]> wrote in
message news:[email protected]...
>
> I apologize, I'm a rookie at some of this...
>
> I'm struggling to find a way, outside of VB scripting, to have excel
> calculate a formula for me. I have a formula that pulls from a
> separate sheet, but I'd also like it to use the current sheet as well.
> For example:
>
> =SUMPRODUCT(COUNTIF(Schedule!D*143*:D*147*,"=7:00
> dir")+(COUNTIF(Schedule!D143:D147,"=8:00
> DIR"))+(COUNTIF(Schedule!D143:D147,"=9:00
> DIR")+(COUNTIF(Schedule!D143:D147,"=11:00
> DIR"))+(COUNTIF(Schedule!D143:D147,"=Mail"))))
>
> I'd love to have just the 143 portion of D143 and just the 147 portion
> of D147 taken from a single cell on the current page so that for the
> next column I can specify in a different cell 150 and 152 and have
> those two values adjust the formula to read.
>
> =SUMPRODUCT(COUNTIF(Schedule!D*150*:D*152*,"=7:00 dir"....
>
>
> --
> Brianas
> ------------------------------------------------------------------------
> Brianas's Profile:
http://www.excelforum.com/member.php...o&userid=34766
> View this thread: http://www.excelforum.com/showthread...hreadid=545272
>

[Brianas]

bob, thank you very much, you just saved me a ton of time, i appreciate it.

[Bob Phillips]

You can make it simpler by putting say D143:D147 in A1 and just use

COUNTIF(INDIRECT("Schedule!D"&),"=7:00dir")

Oh yes, you also don't need the =, COUNTIF AND SUMIF assume =

COUNTIF(INDIRECT("Schedule!D"&),"7:00dir")


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Brianas" <[email protected]> wrote in
message news:[email protected]...
>
> bob, thank you very much, you just saved me a ton of time, i appreciate
> it.
>
>
> --
> Brianas
> ------------------------------------------------------------------------
> Brianas's Profile:
http://www.excelforum.com/member.php...o&userid=34766
> View this thread: http://www.excelforum.com/showthread...hreadid=545272
>

[Brianas]

The D value may change though, so as we add and remove columns on the Schedule sheet it may change. So the next logical question is...

Right now I have the value D alone in a cell, I put it there simply for my reference as I wrote the rest of the spreadsheet. If I add a column on the Schedule sheet, somewhere in the middle, this value will change. If I keep that reference cell (the one with the D) on the worksheet you've helped me with and I change that value in the future, what is the best way to flood those changes across?

If D143 needs to be changed to G143 is there an easy way to do this? Can I flood it similar to the same way we use indirect here --- (INDIRECT("Schedule!d"&F1&":d"&F2) ?

[Bob Phillips]

You could use

INDIRECT("Schedule!"&F1&LEFT(F2,FIND(":",F2)-1)&":"&F1&RIGHT(F2,LEN(F2)-FIND
(":",F2)))

Where F1 holds D (or G) and F2 has 142:147

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Brianas" <[email protected]> wrote in
message news:[email protected]...
>
> The D value may change though, so as we add and remove columns on the
> Schedule sheet it may change. So the next logical question is...
>
> Right now I have the value D alone in a cell, I put it there simply for
> my reference as I wrote the rest of the spreadsheet. If I add a column
> on the Schedule sheet, somewhere in the middle, this value will change.
> If I keep that reference cell (the one with the D) on the worksheet
> you've helped me with and I change that value in the future, what is
> the best way to flood those changes across?
>
> If D143 needs to be changed to G143 is there an easy way to do this?
> Can I flood it similar to the same way we use indirect here ---
> (INDIRECT("Schedule!*d*"&F1&":*d*"&F2) ?
>
>
> --
> Brianas
> ------------------------------------------------------------------------
> Brianas's Profile:
http://www.excelforum.com/member.php...o&userid=34766
> View this thread: http://www.excelforum.com/showthread...hreadid=545272
>