Entering a formula depending on criteria of cell

[drgogo]

Heres a Puzzles

I know that you can have a cell show any thing if a certain cell conatins data

i.e if B2 had this formula" =(A2="Y","1","0") " then B2 would show a 1 or 0 depending whether A2 had a Y in the cell. This might be incorrect but you get the gist.

Well I have sorting issue, which i have managed to sortout cos i have blanks in the column.

I have now found a way around the problem, but it would be reversing the formula. i.e.

if Cell A2 is populated then enter "formula" in D2 else leave D2 blank

The A column is populated with a ref number when the user enters data into D column
But if nothing is entered into D then there is no ref eneterd into A

Hope this explains stuff

G

[TYork]

Your problem sounds circular in nature? Column A is dependent upon Column D but you want to put a formula in column D if there is something in column A?

You can try the following in D2 but I'm not sure I fully understand your problem.

=If(isblank(a2)=true,"",[some formula])

[JBeaucaire]

TYork, watch the forums in use...this Q was posed in the VBA forum, so the user expects a programming response, typically.

drgogo, try this:

1) Right-click on the sheet tab and select VIEW CODE
2) Paste in this sheet-event macro

Please Login or Register  to view this content.

3) Close the editor
4) Save your workbook as a macro-enabled workbook
5) Make an entry in column A and "formula" should appear in column D.

[JBeaucaire]

Be sure to feedback if the solution worked or not.

[drgogo]

Thank you fro that bit of code, I followed your instructions, but have code already on that sheet. So i tried to fit it in the code already there and its not working, I dunno if its the way i have entered i.e. in wrong order or where the formula is not working cause its an Array..

i.e. has {} back and front, as its was original enetred in as a array into the cell.

Code for worksheet as it is at the moment

Please Login or Register  to view this content.

Thanks

G

[JBeaucaire]

1) You don't need to both exit the sub if the target.cells.count > 1 and do a FOR/NEXT loop on all the cells in the target...one or the other. I usually leave in the FOR/NEXT loop and remove the target.cells.count > 1 code as unneeded.

2) No clue about the rest of that, not part of this current topic.

[drgogo]

Sorry a bit of a newwbie to this,

I have tried taking out the target.cells.count > 1 code but still not working.

The orginal formula works fine, just does not want to work within the exsiting code or understand the array... ??

G

[drgogo]

This formula works great but as i said, my formula is an array, so i just cant enter it in. I have looked throught the internet on how to do this but they expect the formulaarray to follow a range.

I dont see the point in reposting this as a different question as it is part of the same problem. as its a formula i need to insert into a cell depending on a certain criteria

Please Login or Register  to view this content.

Would like to say thanks to all that have help, I know you have better things to do than help us newwbies lol

cheers you are all stars

[StephenR]

Adapting JB's code a little, something along these lines? I don't understand your previous code because it seems to contradict itself.

Please Login or Register  to view this content.

I know you have better things to do than help us newwbies lol

Not at all, it is the raison d'etre of this place and places like it.

[drgogo]

Strange thing is i tried adding .formulaArray and it came up with errors.

the code which is contridicting it self is explained like this

I already have this code on my worksheet

Please Login or Register  to view this content.

and what im trying to do if this code works

Please Login or Register  to view this content.

is to try and to put the two together

G

[StephenR]

Can you attach a sample workbook? Always helps.

[JBeaucaire]

I've had problems in the past entering a .FormulaArray reference into a range of cells all at once. They all seemed to take the formula from the first cell in the range and no adjustments occur for relative references.

I had to enter the formula as an array into the first cell, then use a copy method for the rest of the range...AutoFill or Copy/Paste...both work fine.

[drgogo]

I agree, copy and paste method would be better, but the column is hidden and the worksheet is locked, so i need the process to be automated. It also depends on the out come of coulmn A.

Now if Autofill can be done via VBA and work in the same way as in, if the cell in A is populated then autofill D with array formula..

it would need to be able to follow on from the last populated cell, as there breaks in column.

I have posted an exmapl sheet, I have populated with some data. As you will see the ref numbers are in Col A which are automated. The forumla that needs to be added goes into col E which will be hidden. So when a fig is eneterd in Col D Col A is populated and then the formula needs to be added into Col E, this works great for the first one

[StephenR]

Since you can hardly change more than one cell at a time, try this:

Please Login or Register  to view this content.

[drgogo]

Nope that produced the same result as i get at the moment...

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[drgogo]

Brilliant !!!!

You are a god send JB, working perfectly, so far, need a little more testing to double check it.

That one problem sorted, now onto the next

G