Is this possible?

  • Thread starter Thread starter Brianas
  • Start date Start date
B

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!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"....
 
COUNTIF(INDIRECT("Schedule!D"&A1&":D"&B1),"=7:00dir")

etc.

---
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)
 
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)
 
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) ?
 
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)
 
Back
Top