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
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies