Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|