Help with INDIRECT

[Spreadsheet]

I am trying to create a report generator for my spreadsheet. On m
report sheet I want to be able to specify the name of the sheet (B5
and the column heading from that sheet (A9) to pull data from so that:

A10=HLOOKUP(INDIRECT($A$9),INDIRECT("'"&$B$5&"'!A2:AA1000"),2)
A11=HLOOKUP(INDIRECT($A$9),INDIRECT("'"&$B$5&"'!A2:AA1000"),3)
A12=HLOOKUP(INDIRECT($A$9),INDIRECT("'"&$B$5&"'!A2:AA1000"),4)
...

In A9 the user can select valid column headings from a list. Fo
example, if B5=Sheet 1 and A9=Name then A10 would equal the first entr
under the column heading "Name" from Sheet 1, A11 would equal th
second, ....

However, the formula I have listed above does not work for th
INDIRECT($A$9) part (the rest seems to work). I tried simply A9 instea
of INDIRECT($A$9), and the report did pull data from Sheet 1, it jus
wasn't from the Name column, it was from some other column. Can anybod
tell me how to correct this formula? Thanks for the help

 

[Spreadsheet]

Hi, I solved my own problem, it was just a simple mistake. There was n
need for INDIRECT anyways. I just forgot to include the row of colu
headings in the array reference. Here's the working formula:

A10=HLOOKUP($A$9,INDIRECT("'"&$B$5&"'!A1 :AA1000"),2)
A11=HLOOKUP($A$9,INDIRECT("'"&$B$5&"'!A1 :AA1000"),3)
A12=HLOOKUP($A$9,INDIRECT("'"&$B$5&"'!A1 :AA1000"),4

 

[Spreadsheet]

Now my question is, How can I make each column of the report forma
numbers the same way as in the column from which the data is bein
extracted? I need the report to be able to show dates, percentages
currency, ... Thanks in advance for any help

 

[Peo Sjoblom]

You need to copy, formulas do not carry over formats

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Spreadsheet" <[email protected]>
wrote in message
news:[email protected]...