SOLVED

Number formatting such that only the first number in the column has the $ sign

%3CLINGO-SUB%20id%3D%22lingo-sub-2087028%22%20slang%3D%22en-US%22%3ENumber%20formatting%20such%20that%20only%20the%20first%20number%20in%20the%20column%20has%20the%20%24%20sign%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2087028%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20an%20excel%20file%20which%20contains%20numbers%20and%20NAs%20%2F%20NMs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20the%20number%20of%20rows%20in%20the%20table%20remains%20the%20same%2C%20the%20numbers%20the%20column%20and%20their%20position%20in%20the%20table%20change%20every%20time%20I%20update%20the%20data.%20The%20excel%20contains%20a%20ton%20of%20data%20and%20formatting%20it%20becomes%20a%20hassle%2C%20as%20with%20each%20file%20update%2C%20cells%20with%20numbers%20that%20were%20previously%20available%20are%20now%20NA%20%2F%20NM%2C%20and%20some%20other%20NA%20%2F%20NM%20cells%20now%20have%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20one%20of%20you%20kind%20folks%20can%20help%20me%20automate%20this%20such%20that%20--%3C%2FP%3E%3CP%3ENo%20matter%20where%20the%20'first'%20number%20(from%20top%20to%20bottom)%20in%20a%20given%20column%20appears%2C%20it%20will%20now%2C%20carry%20the%20%24%20sign%20as%20prefix%20while%20other%20numbers%20below%20it%20and%20the%20NAs%20%2F%20NMs%20above%20or%20below%20it%20(if%20any)%20stay%20unaffected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20the%20Community%20members%20are%20kind%20enough%20to%20help%20me%20save%20at%20least%2030%20min%20each%20week%20by%20helping%20me%20here.%20Have%20attached%20an%20excel%20that%20shows%20examples%20of%20the%20output.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20thanks%20for%20your%20help%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2087028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2087142%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20formatting%20such%20that%20only%20the%20first%20number%20in%20the%20column%20has%20the%20%24%20sign%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2087142%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F939088%22%20target%3D%22_blank%22%3E%40SeeingAhead%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20if%20you%20select%20the%20range%20to%20format%2C%20and%20with%20the%20top%20left%20cell%20as%20the%20active%20cell%20(A1%20in%20your%20example%20workbook)%2C%20use%20a%20conditional%20format%20formula%20(change%20the%20cell%20reference%20as%20applicable)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dcount(a%241%3Aa1)%3D1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20select%20your%20custom%20number%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear Community,

 

I am working on an excel file which contains numbers and NAs / NMs.

 

While the number of rows in the table remains the same, the numbers the column and their position in the table change every time I update the data. The excel contains a ton of data and formatting it becomes a hassle, as with each file update, cells with numbers that were previously available are now NA / NM, and some other NA / NM cells now have numbers.

 

I am hoping one of you kind folks can help me automate this such that --

No matter where the 'first' number (from top to bottom) in a given column appears, it will now, carry the $ sign as prefix while other numbers below it and the NAs / NMs above or below it (if any) stay unaffected.

 

I hope the Community members are kind enough to help me save at least 30 min each week by helping me here. Have attached an excel that shows examples of the output. 

 

Again, thanks for your help in advance. 

2 Replies
Best Response confirmed by SeeingAhead (New Contributor)
Solution

@SeeingAhead 

 

I believe if you select the range to format, and with the top left cell as the active cell (A1 in your example workbook), use a conditional format formula (change the cell reference as applicable):

 

=count(a$1:a1)=1

 

and select your custom number format.