#1   Report Post  
Posted to microsoft.public.excel.misc
Brianas
 
Posts: n/a
Default Is this possible?


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Is this possible?

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

etc.

---
HTH

Bob Phillips

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

"Brianas" wrote in
message ...

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Brianas
 
Posts: n/a
Default Is this possible?


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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Is this possible?

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" wrote in
message ...

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



  #5   Report Post  
Posted to microsoft.public.excel.misc
Brianas
 
Posts: n/a
Default Is this possible?


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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Is this possible?

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" wrote in
message ...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"