Edit cell in ARRAYFORMULA

[stevenmb24]

In column A I have this formula:

= ARRAYFORMULA (IF(B1:B="Custom Rate","*enter rate", ARRAYFORMULA (IF(B1:B="High Rate",100,ARRAYFORMULA (IF(B1:B="Medium Rate",50,ARRAYFORMULA (IF(B1:B="Low Rate",20,""))))))))

And in column B I have a drop-down menu with the choices "Custom Rate, High Rate, Medium Rate, Low Rate". When a rate is chosen the corresponding value in the ARRAYFORMULA populates the cell in column A. I would like the value for "Custom Rate" to be editable.

Is there a way to have the value be equal to a wildcard character? Or is there any other way to allow me to edit the value?

Also, the "Custom Rate" value will always be a number between 1-100, if that helps.

[oeldere]

if I understand the question well

a2 =VLOOKUP(B2,$F$2:$G$5,2,0) and drag down

see the attached file.

[jason.b75]

The formula you're using doesn't need to be an array, a regular formula with relative references filled down will work just as well.

=IF(B2="Custom Rate","*enter rate", IF(B2="High Rate",100,IF(B2="Medium Rate",50,IF(B2="Low Rate",20,""))))

Or using oeldere's lookup method without the extra table.

=IF(B2="","",LOOKUP(B2,{"c","h","l","m"},{"*enter rate",100,20,50}))

[Richard Buttrey]

Hi and welcome to the forum.

It's not clear to me why you need an array formula. A standard

Please Login or Register  to view this content.

will surely do.

You will need a Sheet Change macro to enable you to enter a value between 1 & 100 in column A though. i.e.

Please Login or Register  to view this content.

[AlKey]

@jason.b75
The last formula doesn't return correct values
Try this

Please Login or Register  to view this content.

[jason.b75]

You will need a Sheet Change macro to enable you to enter a value between 1 & 100 in column A though.

Any reason why, Richard? There is no validation on column A, so once the array is removed, editing should be possible.

@jason.b75

That's because I had a bit of a Doh! moment and ordered the wrong array constant. I've fixed it now.

[stevenmb24]

Thanks everyone! I didn't realize there were so many ways to solve my issue

[oeldere]

You're welcome. We appreciate the feedback!

Thanks for marking the question solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

[Richard Buttrey]

Any reason why, Richard? There is no validation on column A, so once the array is removed, editing should be possible.

Yes, because if a manual entry is made in column A then the formula is over-ridden. If subsequently the user chooses something other than the Custom Rate in column B then the adjacent cell in column A won't change.

It just seemed tidier to me to make sure that either a validation is set or a formula entered in column A whenever column B is changed. Hence a sheet change macro.

[jason.b75]

Good point, Richard.