Home

Using named array in a conditional format formula

%3CLINGO-SUB%20id%3D%22lingo-sub-418801%22%20slang%3D%22en-US%22%3EUsing%20named%20array%20in%20a%20conditional%20format%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418801%22%20slang%3D%22en-US%22%3E%3CP%3ESalutations%20and%20G'd%20day%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20array%2C%20%24C%243%3A%24C%24102%20that%20is%20named%26nbsp%3B%3CSTRONG%3ESummary_Expense_Center%3C%2FSTRONG%3E.%20I%20am%20trying%20to%20use%20this%20name%20in%20a%20formula%20to%20conditionally%20format%20%24D%24%3CEM%3En%3C%2FEM%3E%3A%24F%24%3CEM%3En%3C%2FEM%3E%26nbsp%3Bwhere%20%3CEM%3En%3C%2FEM%3E%20is%20the%20current%20row%20(%40)%20in%20those%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I'm%20using%20in%20the%20conditional%20format%20is%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D(%40Summary_Expense_Center%26lt%3B%26gt%3B%22%22%2C1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Summary_Expense_Center%20is%20the%20spill%20of%20an%20array%20formula.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20true%20I%20change%20the%20background%26nbsp%3Bcolor%2C%20if%20false...%20no%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20typed%20myself%20bloody%20trying%20to%20find%20a%20go%20or%20no-go%20on%20the%20use%20of%20named%20arrays%20used%20this%20way.%20No%20mention%20either%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20all%20for%20a%20hint%2C%20pointer%2C%20affirmation%26nbsp%3Bor%20denial%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%3C%2FP%3E%3CP%3E(John)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-418801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-419082%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20named%20array%20in%20a%20conditional%20format%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-419082%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESame%20as%20with%20tables%2C%20INDIRECT.%20On%20this%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20162px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106398i10223CC3F8AFCB09%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3D%40INDIRECT(%22Summary_Expense_Center%22)%3D5%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317593%22%20target%3D%22_blank%22%3E%40TheOldPuterMan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-420727%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20named%20array%20in%20a%20conditional%20format%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-420727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317593%22%20target%3D%22_blank%22%3E%40TheOldPuterMan%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJohn%2C%20glad%20to%20help.%20Good%20luck%20with%20dynamic%20arrays%2C%20there's%20a%20lot%20of%20news%20here%20for%20each%20of%20us.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-420645%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20named%20array%20in%20a%20conditional%20format%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-420645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20A%20very%20simple%20yet%20elegant%20solution%20to%20a%20rather%20vexing%20problem.%20A%20slight%20adaptation%20and%20I%20was%20able%20to%20accomplish%20my%20goals!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E
TheOldPuterMan
Occasional Contributor

Salutations and G'd day,

 

I have an array, $C$3:$C$102 that is named Summary_Expense_Center. I am trying to use this name in a formula to conditionally format $D$n:$F$n where n is the current row (@) in those columns.

 

The formula I'm using in the conditional format is:

=(@Summary_Expense_Center<>"",1)

 

(Summary_Expense_Center is the spill of an array formula.)

 

If true I change the background color, if false... no change.

 

I've typed myself bloody trying to find a go or no-go on the use of named arrays used this way. No mention either way.

 

Thanks all for a hint, pointer, affirmation or denial

 

TheOldPuterMan

(John)

 

 

3 Replies

Hi John,

 

Same as with tables, INDIRECT. On this sample

image.png

=@INDIRECT("Summary_Expense_Center")=5

@TheOldPuterMan 

@Sergei Baklan 

 

Sergei,

 

Thank you! A very simple yet elegant solution to a rather vexing problem. A slight adaptation and I was able to accomplish my goals!

 

Much appreciated,

 

TheOldPuterMan

 

John

@TheOldPuterMan ,

 

John, glad to help. Good luck with dynamic arrays, there's a lot of news here for each of us. 

Related Conversations
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies