Leaving a cell unchanged in a formula

Mel C Roberts · Mar 13th, 2002, 09:01 AM

Hi,
I need to input an IF formula in cell C1 that says if A1 = "Total", then print D1 (which is formula) otherwise leave the contents of C1 as they are.
Is this at all possible??
Thanks so much Mel xx

Audiojoe · Mar 13th, 2002, 09:03 AM

Do you mean that if it doesn't meet the requirement, then you want to leave C1 blank? If so it'd be:

=if(a1="total",d1,"")

Is this what you mean?

Mel C Roberts · Mar 13th, 2002, 09:07 AM

Hi,
No what i mean is that column C1 has a list of numbers in it and I want to write some VB that overights that information ONLY IF A1 says "Total". Therefore when I mean leave it blank I meant to say don't change the information that already exists in that cell.
Cheers for the quick response.
Mel x

Audiojoe · Mar 13th, 2002, 09:19 AM

So do you mean that you want a macro that would go down through the sheet and perform this function whenever you pressed a button?

Mel C Roberts · Mar 13th, 2002, 09:25 AM

Audijoe,
I guess that would be best. In that case i want to be able to highlight three cells (there are three totals in the spreadsheet)eg A1, A10, A20, and return the formula in those cells, overrighting any value that is there but leaving the formatting alone (the format for each cell will be different ie one total background is green, one is blue and one is orange.) This would save the lookup function being used.
Cheers Mel

Audiojoe · Mar 13th, 2002, 09:32 AM

Try this on your sheet. Paste it into a module and run it

Sub GoGeddemBoys()

lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For n = 1 To lastRow
If Cells(n, 1).Value = "total" Then Cells(n, 3).Value = Cells(n, 4).Value
Next

End Sub

Mel C Roberts · Mar 14th, 2002, 04:38 AM

Audiojoe,
I don't know how to thank you!
With a little bit of manipulation i now have a fully functional spreadsheet that does everything i want it to do!!
Thank you so much for your help.
Regards Mel xxxx

Audiojoe · Mar 14th, 2002, 04:42 AM

Not a problem

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 09:01 AM	#1
Mel C Roberts New Member Join Date: Feb 2002 Location: England Posts: 40	Hi, I need to input an IF formula in cell C1 that says if A1 = "Total", then print D1 (which is formula) otherwise leave the contents of C1 as they are. Is this at all possible?? Thanks so much Mel xx

Mar 13th, 2002, 09:03 AM	#2
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Do you mean that if it doesn't meet the requirement, then you want to leave C1 blank? If so it'd be: =if(a1="total",d1,"") Is this what you mean?

Mar 13th, 2002, 09:07 AM	#3
Mel C Roberts New Member Join Date: Feb 2002 Location: England Posts: 40	Hi, No what i mean is that column C1 has a list of numbers in it and I want to write some VB that overights that information ONLY IF A1 says "Total". Therefore when I mean leave it blank I meant to say don't change the information that already exists in that cell. Cheers for the quick response. Mel x

Mar 13th, 2002, 09:19 AM	#4
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	So do you mean that you want a macro that would go down through the sheet and perform this function whenever you pressed a button?

Mar 13th, 2002, 09:25 AM	#5
Mel C Roberts New Member Join Date: Feb 2002 Location: England Posts: 40	Audijoe, I guess that would be best. In that case i want to be able to highlight three cells (there are three totals in the spreadsheet)eg A1, A10, A20, and return the formula in those cells, overrighting any value that is there but leaving the formatting alone (the format for each cell will be different ie one total background is green, one is blue and one is orange.) This would save the lookup function being used. Cheers Mel

Mar 13th, 2002, 09:32 AM	#6
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Try this on your sheet. Paste it into a module and run it Sub GoGeddemBoys() lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row For n = 1 To lastRow If Cells(n, 1).Value = "total" Then Cells(n, 3).Value = Cells(n, 4).Value Next End Sub

Mar 14th, 2002, 04:38 AM	#7
Mel C Roberts New Member Join Date: Feb 2002 Location: England Posts: 40	Audiojoe, I don't know how to thank you! With a little bit of manipulation i now have a fully functional spreadsheet that does everything i want it to do!! Thank you so much for your help. Regards Mel xxxx

Mar 14th, 2002, 04:42 AM	#8
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Not a problem