asked on

Colum names typed should get the colums to the new sheets

Hi,
I have a excel sheet that has 30+ colums of data.Different time i need to take different report which varies of different colums of data.
Is there a way that i have a sheet called"Reports" when typed the colum names exactly on row 1 it should get all the ciolums data to the "Reports" sheet.
"The sheetname is "Desktops" where i have the data.

Regards
Sharath

jeff_uk

If you can make the column titles in alphabetical order left to right (either by moving them, or prefixing their titles with an index number) you can use HLOOKUP

See attached excel file for an example. Change the column titles to one of name, occupation, payband or address' and the data in the column will change.

I've had to use an index column on the lefy hand side to tell the HLOOKUP which row we're interested in, and it needs some tidying up, (working with formats or an 'IF' wrapper to get rid of the 0's, and hiding the index column) But i think this is what you need.

LookupExample.xls

bsharath

ASKER

Thanks for the responce.

I cannot change the colums.As they are very much related to the other macros in the file.
Any way to just do as the colums are.

Colum names mentioned in the Reports sheet.When run macro to get the colums to the sheet from the "Desktiops" sheet.

ASKER CERTIFIED SOLUTION

jeff_uk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bsharath

ASKER

jeff_uk
Where should i put the header fields?
In the reports sheet when i type "Job" and run the macro.Nothing happens.Am i doing some thing wrong

bsharath

ASKER

jeff_uk
Where should i put the header fields?
In the reports sheet when i type "Job" and run the macro.Nothing happens.Am i doing some thing wrong

jeff_uk

you don't run the macro, you use '=findData(Headerfield,ROW)' like in my example.. the function() routine creates a user defined function that you can use anywhere in any spreadsheet.

you put the header fields wherever you want, as long as you point to them using the 'headerfield' value.

bsharath

ASKER

Thanks for this...