Way to make number portion of cell reference a variable?

[ExcelAtEverything]

Hello! My question is simply this. Is there a way to change a cell reference so that the number portion of the cell reference is determined by the contents of another cell? For instance if I have a formula in A1 that will keep delivering back a changing number, then I want to be able to use that changing number in a cell reference in the following manner.

I have a formula in C1 that will need to keep referencing a cell number which is based on the number returned in A1. So in C1 my formula must say something like: =B("A1"). That is of course not correct in any way, but that is the idea of what I need to make happen. Can anyone help me out?

 

[Toadstool]

Hi ExcelatEverything,

I'm not sure I'm following what you want to achieve but INDIRECT may be the way to go.

eXCELATeVERYTHING.xlsx
	A	B	C	D	E
1	1		A		A
2	2		B		B
3	3		C		C
4	4		D		D
5	5		E		E
6	6		F		F
7	7		G		G
8	8		H		H
9	9		I		I
Sheet1

Cell Formulas
Range	Formula
C1:C9	C1	=INDIRECT("E"&A1)

 

[ExcelAtEverything]

Thanks Toadstool! I'm not sure that'll do it. Check out my 2 attachments, 1 attachment for each tab. What I want to be able to do is that every time I add a new store into a new row at the bottom of the DATA tab list (like when a new store opens and comes online), it will create a new formatted row into the bottom of each of the Yesterday, MTD, and YTD charts on the front tab and enter.

That is what I'm trying to thumb my way thru, and I'm trying to do it without using code (although Macros would be fine), mostly because I'm not very familiar with coding above general macro recording and some mild editing of the macro.

Does this help you understand what I'm trying to do?

 

[Toadstool]

Excel won't automatically extend a table based on a formula and it can't reposition the MTD and YTD tables based on the Yesterday being extended.
You could make the MTD and YTD columns and fill the range plus unused rows with formulae which return a null or take the next STORE entry, but you'd need to add some Conditional Formats to simulate how your example appears.

To do what you ask would need VBA.

 

[Fluff]

Duplicate to: Table's number of columns needs to expand based on cell value

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.