Hi,
I have create a file in which i insert sum formula from other sheet. now i want to insert $ sign in all cells. it is difficult to insert one by one.
Is there any way to Fix/insert $ sign in all formula based cells at once?
Hi,
I have create a file in which i insert sum formula from other sheet. now i want to insert $ sign in all cells. it is difficult to insert one by one.
Is there any way to Fix/insert $ sign in all formula based cells at once?
Select all the cells and format as currency.
Ben Van Johnson
Ben, I *think* the OP is talking about absoluting?
Imram, if I am correct here, what do your formulas look like?
Have you considered using Find/Replace?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Ford, i tried to find/Replacing but it take lot of time because in some of cells contains sum formula and some just = from other sheet.
Imran,
Turn the Calculation option to Manual.
Do Find & replace as Ford advised, once you're done, Turn the Calculation back to Automatic.
Regards,
Khalid
Formula:Please Login or Register to view this content.
and the other is
Formula:Please Login or Register to view this content.
If that is all you need to do, do a Find !D and Replace !$D$
then repeat that with :D and :$D$
i have different column ranges From C to CF. It means i have to go to individual column and find/replace.
Yes, might be.
But if you can upload a sample, someone may offer other solution, may be a drag down/right formula.
I have upload my file. Now i have tow sheets one is Main Data and second is Categorize.
Categorize sheet collect data from Main Data Sheet. now i want to Transpose data from the Categorized sheet because i will work it on macro/VBA.
when i am trying to copy and pest as Formula & Transpose but the values/range changed.
Hi Imran,
I am afraid you may not get solution for find & replace.
But you can use the single formula, like SUMIF...
use this in Categorized sheet C4:
=SUMIF('Main Data'!$B$4:$B$211,"*"&$B4&"*",'Main Data'!C$4:C$211)
Copy down and across...
Make sure to set the matching strings in column b, for example B5 should be IUD instead of IUDs
and so on for others, its up to you.
Regards,
I came up with the same as Khalidngo...
=SUMIF('Main Data'!$B$2:$B$500,"*"&Categorized!$B4&"*",'Main Data'!C$2:C$500)
However, you will need to make sure that the terms you use in Categorize col B are contained (somewhere)) in col B Main.
For instance, in Cat sheet, you have UIDs but in Main you only have blah UID blah (no s)
You have Short Terms in Cat but that term does not exist in Main
See what I mean? The SUMIF is searching for matching terms
Oh, and by the way, to do what you initially asked, all you need to do is remove the $ in front of the column letter, then copy across...
from ='Main Data'!$C$4+'Main Data'!$C$5
to ='Main Data'!C$4+'Main Data'!C$5
very long way to get your answers though
thanks guys i will try and let you know. Thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks