Referencing a string of charcters in a cell as a cell reference.

[cguy3000]

Alright, so I have looked everywhere online and cant even find a specific way to do what I need or even if there is a way to do it.

So right now I am trying to create a list of dates that always displays the last 8 entries of dates in a data pool. So when I add a new date with values it will automatically update and shift everything down one to not display the 9th entry in excel and now display the new date entry as entry 1. My issue is I have a formula to display the beginning cell reference of the 8 cells ecept I dont know how to have excel read it as a cell reference instead of couple of equations.

For example I have a formula that spits out the value B345, which is the first cell in these 8 data points I want to display, which is made by the equation =B"&R345. The cell R345 references an equation to give me a number which then I add on the B to give me a cell reference B345. I want to then have excel read the B345 value as a reference to a cell instead of the result of the equation =B"&R345. I want it to reference a cell number for what I am trying to do. Any thoughts on how to do that??

 

[mikerickson]

Have you looked at the INDIRECT function?

Also have you considered the formula =INDEX(B:B,R345,1) to refer to the R345th row of column B?

 

[cguy3000]

So when i use that it returns a value of zero for me. Here, I dont think I explained it well enough for you to understand;

I have a list of dates. say [TABLE="width: 223"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4/5/2019[/TD]
[/TR]
[TR]
[TD="align: right"]4/12/2019[/TD]
[/TR]
[TR]
[TD="align: right"]4/19/2019[/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2019[/TD]
[/TR]
[TR]
[TD="align: right"]5/10/2019
[/TD]
[/TR]
[TR]
[TD="align: right"]And every week I add on an additional date. I have cell that tells me which cell is the last filled cell;

=COUNTA(A98:A343)

So that equation tells me which cell row contains my last inputed date so this value will change each time I add a new date.[/TD]
[/TR]
[TR]
[TD="align: right"]
What I am trying to do is create cells that reference say the fourth cell from the last inputed date, the third cell from the last inputed date, the 2nd cell from the last inputed date all seperately. This way I can create a cycle that everytime I add a new date these values will automatically update themselves and go 4,3,or 2 dates back to the new values. [/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

If your dates are in column A and =COUNTA(A98:A343) is the number of dates in the list (presumably everything above A98 is not a date of interest), then

=INDEX(A:A, 97+COUNTA(A98:A343), 1) should return the last date.

 

[cguy3000]

That seems like it would work as I am looking at it, but it only returns a value of 43621 with decimal points.

Any other thoughts?

 

[mikerickson]

Is the date with the formula formtted as General or in one of the date formats?

 

[cguy3000]

Yeah, the cell is formatted to date with just the day, month, and year.