Hello,
I have a dataset that is downloaded. It comes in a series of tables with a cell that identifies which table. I can search for "Late Finish Date" and that tells me which row it's table starts at. And, I can search for the next table which tells me the last row of "Early Start Date" and the first row of "Early Finish Date." If you look at my spreadsheet, you'll see the row number where I found "Early Start Date" in CELL E4 (Value=922). The row where I found "Early Finish Date" is in CELL E5.
I want to use those values to redefine my named range when I refresh the dataset. In the Name Manager tool, I added this formula: = OFFSET(A1,(E4),1) : OFFSET(A1,(E5-2),4). Excel changes it to: = OFFSET('NewComparison (002)'!A1,('NewComparison (002)'!E4),1) : OFFSET('NewComparison (002)'!A1,('NewComparison (002)'!E5-2),4). This appears to work. It selects the right cells when I am using the tool. But, I can't get it to work with a formula, even with a simple INDEX function. The range name doesn't show up in that box on the top left of the spreadsheet.
Is there another way?
Thanks
Bookmarks