| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named ranges are causing a #REF error
Hey, Everyone!
I have a table for chart data that changes two aspects depending on user input: the sheet it pulls data from and the range on that sheet it pulls data from. In the main sheet SideBySide, the user chooses a city in $B$2 (sheet name) and a product in $D$2 (range name). This formula works for the sheet switching only... =IF(A40,SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!$B$4:$B$14200")=A 4),INDIRECT("'"&SideBySide!$B$2&"'!$C$4:$C$14200") ,INDIRECT("'"&SideBySide!$B$2&"'!$G$4:$G$14200")), "") The range that needs to change is in the second INDIRECT ($C$4:$C$14200 corresponds to choosing the product Hypo.) However, I also have products HCL (found in $D$4:$D$14200) and Caustic ($E$4:$E$14200) I replaced the second INDIRECT's static range with SideBySide!$D$2, hoping to get Tacoma!Caustic for instance, but I get a #REF error. ,INDIRECT("'"&SideBySide!$B$2&"'!SideBySide!$D$2") , Is this a matter of keeping the quotes and apostrophes straight, or is having the same named ranges on 5 different pages a problem? I'm sure this is very doable. Any help would be greatly appreciated. Thank you for your time. Have a great day! Arlen |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Named ranges are causing a #REF error
Looks like quotes, and exclamations marks is the problem...
INDIRECT("'" & SideBySide!$B$2 & "'!" & SideBySide!$D$2), -- HTH... Jim Thomlinson "Arlen" wrote: Hey, Everyone! I have a table for chart data that changes two aspects depending on user input: the sheet it pulls data from and the range on that sheet it pulls data from. In the main sheet SideBySide, the user chooses a city in $B$2 (sheet name) and a product in $D$2 (range name). This formula works for the sheet switching only... =IF(A40,SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!$B$4:$B$14200")=A 4),INDIRECT("'"&SideBySide!$B$2&"'!$C$4:$C$14200") ,INDIRECT("'"&SideBySide!$B$2&"'!$G$4:$G$14200")), "") The range that needs to change is in the second INDIRECT ($C$4:$C$14200 corresponds to choosing the product Hypo.) However, I also have products HCL (found in $D$4:$D$14200) and Caustic ($E$4:$E$14200) I replaced the second INDIRECT's static range with SideBySide!$D$2, hoping to get Tacoma!Caustic for instance, but I get a #REF error. ,INDIRECT("'"&SideBySide!$B$2&"'!SideBySide!$D$2") , Is this a matter of keeping the quotes and apostrophes straight, or is having the same named ranges on 5 different pages a problem? I'm sure this is very doable. Any help would be greatly appreciated. Thank you for your time. Have a great day! Arlen |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Named ranges are causing a #REF error
Jim,
I haven't tried this out on my actual file, but I make a quick sample and everything is grand! Thank you, sir! Arlen "Jim Thomlinson" wrote: Looks like quotes, and exclamations marks is the problem... INDIRECT("'" & SideBySide!$B$2 & "'!" & SideBySide!$D$2), -- HTH... Jim Thomlinson "Arlen" wrote: Hey, Everyone! I have a table for chart data that changes two aspects depending on user input: the sheet it pulls data from and the range on that sheet it pulls data from. In the main sheet SideBySide, the user chooses a city in $B$2 (sheet name) and a product in $D$2 (range name). This formula works for the sheet switching only... =IF(A40,SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!$B$4:$B$14200")=A 4),INDIRECT("'"&SideBySide!$B$2&"'!$C$4:$C$14200") ,INDIRECT("'"&SideBySide!$B$2&"'!$G$4:$G$14200")), "") The range that needs to change is in the second INDIRECT ($C$4:$C$14200 corresponds to choosing the product Hypo.) However, I also have products HCL (found in $D$4:$D$14200) and Caustic ($E$4:$E$14200) I replaced the second INDIRECT's static range with SideBySide!$D$2, hoping to get Tacoma!Caustic for instance, but I get a #REF error. ,INDIRECT("'"&SideBySide!$B$2&"'!SideBySide!$D$2") , Is this a matter of keeping the quotes and apostrophes straight, or is having the same named ranges on 5 different pages a problem? I'm sure this is very doable. Any help would be greatly appreciated. Thank you for your time. Have a great day! Arlen |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Named ranges are causing a #REF error
Jim,
If you have time...why do you need the single quotes around the first range and not the second? When I was using $C$4:$C$14200, I used quotes in front of that. If you have time. Thanks again. Arlen "Jim Thomlinson" wrote: Looks like quotes, and exclamations marks is the problem... INDIRECT("'" & SideBySide!$B$2 & "'!" & SideBySide!$D$2), -- HTH... Jim Thomlinson "Arlen" wrote: Hey, Everyone! I have a table for chart data that changes two aspects depending on user input: the sheet it pulls data from and the range on that sheet it pulls data from. In the main sheet SideBySide, the user chooses a city in $B$2 (sheet name) and a product in $D$2 (range name). This formula works for the sheet switching only... =IF(A40,SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!$B$4:$B$14200")=A 4),INDIRECT("'"&SideBySide!$B$2&"'!$C$4:$C$14200") ,INDIRECT("'"&SideBySide!$B$2&"'!$G$4:$G$14200")), "") The range that needs to change is in the second INDIRECT ($C$4:$C$14200 corresponds to choosing the product Hypo.) However, I also have products HCL (found in $D$4:$D$14200) and Caustic ($E$4:$E$14200) I replaced the second INDIRECT's static range with SideBySide!$D$2, hoping to get Tacoma!Caustic for instance, but I get a #REF error. ,INDIRECT("'"&SideBySide!$B$2&"'!SideBySide!$D$2") , Is this a matter of keeping the quotes and apostrophes straight, or is having the same named ranges on 5 different pages a problem? I'm sure this is very doable. Any help would be greatly appreciated. Thank you for your time. Have a great day! Arlen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Query causing #ref error in spreadsheet | Excel Discussion (Misc queries) | |||
| cells without values causing error message | Excel Discussion (Misc queries) | |||
| Named range causing really weird problems - HELP | Excel Discussion (Misc queries) | |||
| VLOOKUP Formula causing an error | Excel Discussion (Misc queries) | |||
| Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Linear Mode
