Forum Discussion
Compound If statements
SergeiBaklan I have most of the formulas working nicely... thanks to your ideas. However I have not figured out how to identify a price based on the number of transactions (see highlighted in yellow). Sorry, I couldn't follow your formula. Could you provide another hint?
If you clean your data in general you don't need helper column in Master Data, in place of H18
it could be
=INDEX('Master Data'!$G$19:$G$90,MATCH($B$4&$B$18,'Master Data'!$C$19:$C$90&'Master Data'!$D$19:$D$90,0))
or
=XLOOKUP($B$4&$B$18,'Master Data'!$C$19:$C$90&'Master Data'!$D$19:$D$90,'Master Data'!$G$19:$G$90,"NA")
For the next block
I'd add helper column in Master Data to indicate combination, like
with it formulas could be
=LOOKUP($E21,'Master Data'!$F$100:$F$104,'Master Data'!$H$100:$H$104)
and
=LOOKUP($E21,1/($B$4='Master Data'!$C$94:$C$160)/('Biller Overview'!$B$18='Master Data'!$J$94:$J$160)*'Master Data'!$F$94:$F$160,'Master Data'!$I$94:$I$160)
Sorry, I don't know if that's possible. I'd suggest to ask as separate question (open new conversation).
SergeiBaklan so how am I suppose to "protect" check boxes so the User can tab to the field and use either the mouse or space bar to insert a check? Thanks!
You may define/edit it in Name Manager
but more easy way is to select the range and enter the name into Name box as it is done, for example, for MUFG
SergeiBaklan Where are the indirect values defined? I can't find Citi CCB anywhere in the spreadsheet. How do you look at those indirect values? I'll be adding new Channel Partners like Citi... how can this easily be done? Thank you for your help!
INDIRECT() doesn't work only for Citi, but this name isn't defined
If protect Master Data sheet (or part of it to which check boxes are linked) user can't change check box status, it'll be the message as
SergeiBaklan I tried that.. but still spaced out the text without leave a check. Also, I added a Channel Partner and the =INDIRECT(B4) in cell B22 stopped working.. why is that?
For that you shall to protect cells on which check-boxes have references, actually Master Data sheet.
- @Sergei Baklan would you happen to know how I can protect my check boxes so the User doesnt erase it using the space bar?
Thank you for your help on this project...
I feel I am getting to be a better excel developer.I only have a few wrap up issues that I was wonder if you had any suggestions...
Is there a better way of creating the list values rather than the way I have them on the Master Data row 2 - 9 / columns H - T?
I39 = Number of CSR Licenses
Search by CP, Package, CSR, number of licensesH41 = For some reason it can't file Stored at Biller for Aliaswire
i41 = can't find Stored at Aliaswire for MUFG
G58 = I'm not sure what to do there yet
How do I set the Channel Partner field so I can send it to a Channel Partner without them seeing the other Channel Partner data?
How do I protect the Biller Overview page so only the input fields are available?
How do I hide / protect the Master Data so people cant change or see it?
Any suggestions would greatly be appreciated...
We may expand criteria for that column as
('Master Data'!$J$94:$J$160 = B18) OR ('Master Data'!$J$94:$J$160 is empty)
Formula
=LOOKUP( $E21, 1/($B$4='Master Data'!$C$94:$C$160)/ ( ('Biller Overview'!$B$18='Master Data'!$J$94:$J$160)+ (""='Master Data'!$J$94:$J$160) )* 'Master Data'!$F$94:$F$160, 'Master Data'!$I$94:$I$160 )is in Q21 in attached.
SergeiBaklan Hi Sergei, thanks for you tips... worked great! I'm still trying to get the pricing correct in the yellow highlighted areas. In cells I21 - I24, these prices are based on the Channel Partner. Additionally it can be based on the Package, but not always. For example, BMO has different pricing for Basic vs Complete where MUFG just has one pricing. Any ideas? BTW, I'm working on removing the Helper field (B16 - B90) because I don't think its necessary with your formulas, but wanted to see if you had something for the price. ....