Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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: 4879) 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