3

I keep thinking there must be a function that allows an address within a cell to be used as a variable within another cell address. For the types of sheets I'm creating it would make my life so much easier.

For the purposes of example, the function I seek is hypothetically called "THING" below:

Sheet1 Daily Menus

MONDAY MENU
      Items:           Helper Column          Nutritional Data           Results
        A                   B                         C             
1   =Sheet2!A35      =THING(A1,row=35)     =INDIRECT("Sheet3!G"&B1)    =Sheet3!G35
2   =Sheet2!A247     =THING(A2,row=247)    =INDIRECT("Sheet3!G"&B2)    =Sheet3!G247
3   =Sheet2!A989     =THING(A3,row=989)    =INDIRECT("Sheet3!G"&B3)    =Sheet3!G989

TUESDAY MENU
101 =Sheet2!A613     =THING(A101,row=613)  =INDIRECT("Sheet3!G"&B101)    =Sheet3!G613

The bottom line is:

In a complex spreadsheet (10 pages, each with hundreds of rows and columns), I want to manipulate the addresses by altering only the data in column A on page 1.

This would also allow me to easily copy column B down for hundreds of rows. The type of function in column B may also be required in columns C, D, E... for dozens or hundreds of columns.

Right now I'm typing in each needed address manually, or a very long function using the address. If anything changes in column A then I have to go back and retype these long functions... and often I find that I've made a typo with all that typing...

Everett Steed
  • 131
  • 4
  • One more problem: is there any other way to extract the row number (instead of using RIGHT)? Because some row numbers are single-digit, double and triple-digit? If not then I will need to insert 99 rows into each sheet being referenced, then hide them. That's my only low-tech solution to getting hundreds of rows with the same number of digits. – Everett Steed Jan 15 '18 at 06:49
  • @pnuts I have improved the specificity of my question. You have greatly helped my improve my use of INDIRECT and I am very close to a solution I've sought for years. Thx – Everett Steed Jan 15 '18 at 07:48
  • Look at the ROW, COLUMN and ADDRESS functions. – Joe Jan 15 '18 at 08:01
  • @Joe ADDRESS isn't working for me, or perhaps I don't understand how to implement it. The ROW function could work if I put an extra row on Sheet2 and have it number itself, then point the INDIRECT toward that... – Everett Steed Jan 15 '18 at 08:07
  • @Joe If I use ROW(A1) on Sheet1 then I get "1" ... but I really want it to say "35". Is there an easy way to do that? – Everett Steed Jan 15 '18 at 08:23
  • @pnuts - Admirable formula, but inserting 99 rows and using your RIGHT formula may be faster on a spreadsheet this size. – Everett Steed Jan 15 '18 at 08:41
  • BTW I've wondered for about 6 years why Excel doesn't have a quick answer for this type of query. I've encountered this need at least 4 times and have had to create elaborate workarounds for each situation... I can't find anything about this on their Help - you guys have done more for me in the last hour than their lame examples! – Everett Steed Jan 15 '18 at 08:53
  • @ Joe If I insert a row on Sheet2 and use ROW(A1) thru ROW(A999) then I get "item numbers" before the items descriptions. Then I make an extra row on Sheet1 that references the "item number" of the "selected item." Then I can use INDIRECT on Sheet1, col B to reference the item name. That begins to work, if each desired item is selected by entering on Sheet1 "=" and "item number" from Sheet2 (not "=" "item description")... I just thought I would be able to select the items on Sheet1 by clicking "=" and "item description" from Sheet2. That's how I've been selecting up to now (more user friendly) – Everett Steed Jan 15 '18 at 09:10

1 Answers1

2

You seem to have as your starting point formula =Sheet2!A35 in cell A1. What you have to do is:

  1. Get the formula as a string. For this you can use either XL4 macros, VBA, or FormulaText (in Excel 2013 or later), see this.

  2. Extract the row number from the string. For this you an use VBA (very easy), or a formula to find the rightmost letter/$ and keep the substring to its right.

  3. Use the previous answer, as you already incorporated in your question.

Answer to the first version of the question

I will split the formula you are looking for in two steps.

In cell B1 you can enter ="Sheet2!"&"X"&TEXT(A1,"0"). This will produce the result Sheet2!X25. Then in cell C1 you can enter =INDIRECT(B1). This will bring into cell C1 the contents of cell Sheet2!X25.

Notes:

  1. You can copy the formula downwards as you need.

  2. When copying into other columns, take care of relative/absolute indexing.

  3. You could use a single formula (no helper column) in B1: =INDIRECT("Sheet2!"&"X"&TEXT(A1,"0")).

  4. Both Sheet2 and (column) X could be replaced by suitable formulas if you want them not be hardcoded.

  5. Formula in B1 can almost certainly be replaced by ="Sheet2!"&"X"&A1. NB: A long time time ago, under circumstances I do not recall now, I found I needed to use TEXT; YMMV.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • 2
    @EverettSteed - If I understand correctly, what you need is something different from what was asked. It seems to me that this answer addresses the question "Can a variable (esp. a cell's contents) be used as part of a cell address?" I suggest you post another question describing what you need, with a specific example (same as you did here, which was great to see the question, and which is often not seen in SO). Readers might be misled otherwise. – sancho.s ReinstateMonicaCellio Jan 15 '18 at 07:19
  • @EverettSteed - If you happen to post another question, you might add a comment here with a link to it, so they becomed tied and readers can follow through. – sancho.s ReinstateMonicaCellio Jan 15 '18 at 07:24
  • I have improved the specificity of my question. I apologize for the confusion, it was only in exploring pnuts suggestions that I became aware of the true complexity of the question, and the heart of the matter. Any help is appreciated. Thx – Everett Steed Jan 15 '18 at 07:50
  • @EverettSteed - I will try answering the new question. But again, I think you'd better not modified the question, but rather asked a new one, to keep SO useful for others as well. – sancho.s ReinstateMonicaCellio Jan 15 '18 at 08:04
  • Sorry, I just saw your message. I understand the logic of leaving questions in their original form, but I was so enthusiastic to ask what I had intended that I totally missed this message. Sorry – Everett Steed Jan 15 '18 at 09:21
  • Apparently this problem is common enough that one of you smart programmers should develop a user-friendly function that easily does this desirable task... then sell it to Microsoft. – Everett Steed Jan 15 '18 at 09:24
  • @EverettSteed - So did you manage to apply the suggestions for your benefit? – sancho.s ReinstateMonicaCellio Jan 15 '18 at 18:11