Way to make number portion of cell reference a variable?

Status
Not open for further replies.

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,401
Office Version
  1. 2016
Platform
  1. Windows
Hi ExcelatEverything,

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

eXCELATeVERYTHING.xlsx
ABCDE
11AA
22BB
33CC
44DD
55EE
66FF
77GG
88HH
99II
Sheet1
Cell Formulas
RangeFormula
C1:C9C1=INDIRECT("E"&A1)
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Attachments

  • Data Tab.png
    Data Tab.png
    14.7 KB · Views: 4
  • Store Tab.png
    Store Tab.png
    44.9 KB · Views: 4

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,401
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,752
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,128,075
Messages
5,628,498
Members
416,322
Latest member
Corbett

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top