I don't understand why indirect is returning the error. The cell reference is valid and there is a number in the cell being referred to. The number is obtained from a counta function.
The cell with the data is N1 and the indirect statement is in R1.
Thank you in advance...I'm sure this is really simple and I'm overthinking my problem.
What are you trying to do? What value are you expecting in cell R1?
The value in N1. I am going to use it to make a variable reference aka: =indirect("h"&N1). The actual list is much longer than this (hundreds of lines) and will change weekly, so I need a variable line reference to use in a Name (in Name Manager).
Thank you.
So let's say your Name Manager has the Name of "Team17" for cell F17, so you want "Athletics" to show up in R1. The formula in R1 should be:
=INDIRECT("Team"&N1)
Does that help?
No.
I need to create a variable cell reference for a named range because the rows of data will change weekly. In this example, I need the cell reference to be Inventory!$A$2:$H$17. The 17 in the H column needs to be able to expand or contract depending on how many rows of data there are.
I am using the CountA function to count how many rows of data there are, and that is where the line number in N1 comes from.
Sorry for the confusion. I thought Indirect was the correct way to build a variable cell array reference, but apparently not.
Thank you for any help.
Last edited by CWatsonJr; 08-02-2021 at 02:02 AM.
If you need to provide for a variable length table, why not simply use the Excel Table feature which adapts automatically?
I completely concur with Pepe, however this works fine in your workbook:
=INDIRECT("H"&N1)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Are you wanting a dynamic named range?
example: Named range "Var_rng"
=OFFSET(Inventory!$H$2,,,COUNTIF(Inventory!$H$2:$H$1000,"<>"),1)
This seems to work but it's taking a long time for my laptop to compute the workbook. Wouldn't this be the same as using an array such as: Inventory!$a:$h?Originally Posted by Pepe Le Mokko
Thank you.
Could you please explain whet you tried? How many rows? How did you create the starting range?
No! You create the table based on the rows you have now (select the headers and the range of the existing data and then Insert | Table). I have a horrible feeling you've tried to insert a table to the entire worksheet - would this be correct? If so, you don't need to do this: your structured table will grow automatically as you add rows.Wouldn't this be the same as using an array such as: Inventory!$a:$h?
There are instructions at the top of the page explaining how to attach your sample workbook.
If you're looking for a Dynamic Named Range that refers to all the data in columns A to H, from row 2 down, try this:
Formula:
Please Login or Register to view this content.
You don't really need a separate cell, N1, to count the rows but, to demonstrate that:
Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
You're right, that's what I did because I needed the table reference to be dynamic. It's okay though, I tested it on a backup and all is good
I have the sample spreadsheet attached in my original postThank you for the reminder though.
Last edited by CWatsonJr; 08-02-2021 at 11:48 AM.
YES! Thank you. This is exactly what I was looking for.If you're looking for a Dynamic Named Range that refers to all the data in columns A to H, from row 2 down, try this:
Formula:Please Login or Register to view this content.
You don't really need a separate cell, N1, to count the rows but, to demonstrate that:
Formula:Please Login or Register to view this content.
You guys are the best!!!!!!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
@CWatsonJr: You're welcome. Thanks for the rep.
Already done. Again, thank you for the reminder. On both accounts.
You are very welcome and THANK YOU!@CWatsonJr: You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks