Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with INDIRECT
I am trying to create a report generator for my spreadsheet. On my 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. For example, if B5=Sheet 1 and A9=Name then A10 would equal the first entry under the column heading "Name" from Sheet 1, A11 would equal the second, .... However, the formula I have listed above does not work for the INDIRECT($A$9) part (the rest seems to work). I tried simply A9 instead of INDIRECT($A$9), and the report did pull data from Sheet 1, it just wasn't from the Name column, it was from some other column. Can anybody tell me how to correct this formula? Thanks for the help. -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=549565 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with INDIRECT
Hi, I solved my own problem, it was just a simple mistake. There was no need for INDIRECT anyways. I just forgot to include the row of colum 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 ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=549565 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with INDIRECT
Now my question is, How can I make each column of the report format numbers the same way as in the column from which the data is being extracted? I need the report to be able to show dates, percentages, currency, ... Thanks in advance for any help. -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=549565 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with INDIRECT
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" wrote in message ... Now my question is, How can I make each column of the report format numbers the same way as in the column from which the data is being extracted? I need the report to be able to show dates, percentages, currency, ... Thanks in advance for any help. -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=549565 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect Range Referencing | Charts and Charting in Excel | |||
Copying a formulae down a column that includes an INDIRECT | Excel Discussion (Misc queries) | |||
Using INDIRECT in INDEX(LINEST.. ) function | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions |