Display Column Header

0

Dear Excel Forum,    (I m sorry for inconvenience) 

I have a real job problem.  Some subledgers are allotted to particular officers.  A few alphabets (subledgers) are located in data tables with header (dealing officer name). So, when, I type any alphabet then, corresponding header of the table should be populated.

I know very well Vlookup, Nested IF. So, plz try with Indirect or any other formulae. Thanx with hope of prompt reply. (Plz c the attached file)

~ Nitin Shukla

Answer
Discuss

Answers

1
Selected Answer

For the purpose of using the formula below you need to set up a named range "Ledgers" which comprises the table C10:F19 in your example. The names of the officers must be in the row immediately above this range.

=INDEX(OFFSET(INDEX(Ledgers,1,0),-1,0),1,SUMPRODUCT(--(ISERROR(FIND(H11,Ledgers))=FALSE)*COLUMN(Ledgers))-2)

H11 refers to the cell H11 in your example. I tested also H12:H14.

-2 is the number of columns on the left of the named range "Ledgers". In your example Ledgers starts in column C. So there are 2 columns to the left of it and hence -2. This adjustment could have been avoided by calculating that number instead of hard-coding it but I decided asgainst that course of action because it would bloat the formula more than its worth. If you place the Ledgers table in column A no adjustment will be required.

Of course, you can replace the references to Ledgers with hard-coded ranges which would also shorten the formula because OFFSET(INDEX(Ledgers,1,0),-1,0) translates to $C9:$F$9. In fact, in the first version of this formula I used A9:F9 which doesn't require an adjustment. I decided to accept a little extra length for the benefit of grater transparency.

BTW, the problem hails from the FIND function which returns a sheet column. Perhaps another function can be made to return a range column instead.

Discuss

Discussion

Dear Variatus, I am glad to see the answer. Its working very nice.
Fablulous, Astonishing, Amazing & very much helpful to me.
Sir, Thnx.
NDS (rep: 2) Oct 13, '20 at 1:08 pm
A very thoughtful and pleasing accolade. It's my pleasure, NDS.
Variatus (rep: 4148) Oct 13, '20 at 9:33 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login