I had posted a multipart question on another thread & I think it got buried under the original subject title, bc I have not received an answer & am still stuck. here's a message I drafted to a relative, but this was way over his head:
---
what I'm doing is subtotaling (as opposed to pivots which I'm pretty decent at), but need to change the subtotal formulas for each group range. so for example, col F sums the order count (units ordered), but col G *counts* the # of different models ordered. easy enough. I simply find/replace =subtotal(9, with =subtotal(3,.
it start to get tricky when I want to change an entire formula. for example, the subtotal formulas read:
=SUBTOTAL(9,F2:F8)
and
=SUBTOTAL(3,G2:G8)
however, I need cols J &K to be an IF statement.
=IF(COUNTIF(J2:J8,"yes"),"Yes","")
and
=IF(COUNTIF(K2:K8,"Yes"),"Yes","")
the problem I'm running into is that when I collapse the group to level 2 & copy the formula > Go To > Paste Visible, the ranges get out of whack. meaning when I open up the formula (either F2 or double click w/in the cell, the blue range box will often either overlap the group above including extra information (extra yeses yielding false results), or not include the full range (in the above examples of rows 2:8 it may only got to row 6 or 7, excluding 8.
my 2nd *fix* for this is to include cols J & K in my subtotal, & somehow replace the formula w/a custom formula (either by a simple find/replace, or VBA). thoughts?
since I haven't been able to find an easy solution, I'm having to manually adjust each range daily for 1550 rows. extremely time consuming & inefficient. I'm certain there is a way, I'm just unaware. can you help?
---
so, I've spent literally hours googling this & can't find anything that works. I am *not* looking for someone to create the spread sheet for me. I am genuinely interested in learning how to do this for future reference, so i truly appreciate the expertise of the members here on the forum. THANKS!!!!
also, I tried to install the html maker, but couldn't figure out how to get it to work. got an error about 64 bit something or other. I'm sorry, but I'm just not that savvy. also can't even figure out how to include jpgs of screen shots.
---
what I'm doing is subtotaling (as opposed to pivots which I'm pretty decent at), but need to change the subtotal formulas for each group range. so for example, col F sums the order count (units ordered), but col G *counts* the # of different models ordered. easy enough. I simply find/replace =subtotal(9, with =subtotal(3,.
it start to get tricky when I want to change an entire formula. for example, the subtotal formulas read:
=SUBTOTAL(9,F2:F8)
and
=SUBTOTAL(3,G2:G8)
however, I need cols J &K to be an IF statement.
=IF(COUNTIF(J2:J8,"yes"),"Yes","")
and
=IF(COUNTIF(K2:K8,"Yes"),"Yes","")
the problem I'm running into is that when I collapse the group to level 2 & copy the formula > Go To > Paste Visible, the ranges get out of whack. meaning when I open up the formula (either F2 or double click w/in the cell, the blue range box will often either overlap the group above including extra information (extra yeses yielding false results), or not include the full range (in the above examples of rows 2:8 it may only got to row 6 or 7, excluding 8.
my 2nd *fix* for this is to include cols J & K in my subtotal, & somehow replace the formula w/a custom formula (either by a simple find/replace, or VBA). thoughts?
since I haven't been able to find an easy solution, I'm having to manually adjust each range daily for 1550 rows. extremely time consuming & inefficient. I'm certain there is a way, I'm just unaware. can you help?
---
so, I've spent literally hours googling this & can't find anything that works. I am *not* looking for someone to create the spread sheet for me. I am genuinely interested in learning how to do this for future reference, so i truly appreciate the expertise of the members here on the forum. THANKS!!!!
also, I tried to install the html maker, but couldn't figure out how to get it to work. got an error about 64 bit something or other. I'm sorry, but I'm just not that savvy. also can't even figure out how to include jpgs of screen shots.
