0

I have an XLS file with several Sheets named "1", "2", "3", "4", etc.

Now I need a formula that will result in e.g. "='1'!M16" where the name of the Sheet I am referring to is based on current row. So if the current row is 16, the formula would be "='16'!M16".

I created a formula =INDIRECT("'" & ROW() & "'!" & "M16") but Excel for Mac 2011 states "#VALUE" in the cell... What is wrong with my formula, please? Or any other suggestion?

Thanks very much for any help.

Tom

Tom Picka
  • 23
  • 6
  • Works on Excel 2007 in Windows. – Glitch_Doctor Jan 12 '17 at 12:59
  • Are looking to do anything with the data or just view it? I might be able to build a 3D formula to perform calculations with it... – Glitch_Doctor Jan 12 '17 at 13:02
  • Sometimes different spreadsheet applications will handle `INDIRECT`'s volatile behavior different. For example Openoffice or Libreoffice `Calc` also. Not sure for Mac but you could try giving the `ROW` an argument. Starting in a cell in row 1 you use `=INDIRECT("'" & ROW(A1) & "'!" & "M16")`. If you copy this down, it changes automatically to `...ROW(A2)...`, `...ROW(A3)...` and so on. Of course starting in a cell in row 2 you have to start with `=INDIRECT("'" & ROW(A2) & "'!" & "M16")`. – Axel Richter Jan 12 '17 at 13:14
  • Thanks very much - but the result is the same :( the "#VALUE!" value of the cell. I do not know whether it might be connected - but the XLS contains macros. I am getting quite desperate so maybe I am connecting aspects that are not connected :) – Tom Picka Jan 12 '17 at 15:19

2 Answers2

0

Try: =INDIRECT(CONCATENATE(ROW(),"!","M",ROW())

I have a Mac only, it works. Thanks by the way for your question. I learnt thereby

amit.dalmia
  • 86
  • 1
  • 12
  • Sorry, "The formula you typed contains an error" :( I cannot understand what part the Excel does not like... Seems totally correct to me. – Tom Picka Jan 12 '17 at 15:15
  • Tried " =INDIRECT(CONCATENATE("'";ROW();"'!M16")) " - Excel says the formula is correct, but the value is still "#VALUE!" error – Tom Picka Jan 12 '17 at 15:44
  • Seems totally fine to me. I tested it by creating a sheet '12' in same workbook. Put the value 500 in row M16 in the sheet '12'. Got the value '500' in the sheet where I used the formula. You should use the formula in cell M16 in the other sheet though – amit.dalmia Jan 12 '17 at 17:32
  • I give up, cannot understand why the Excel behaves like that :( Thanks very much for the effort though – Tom Picka Jan 12 '17 at 20:37
0

=INDIRECT("'"&ROW()&"'!M16")

Ben I.
  • 1,065
  • 1
  • 13
  • 29
  • Still no go :( The problem is for sure in the "ROW()" function - when replaced with exact number, the formula works – Tom Picka Jan 12 '17 at 15:54
  • What cell are you writing this formula into? Take out the indirect and see where it is pointing: `="'"&ROW()&"'"&"!M16"` – Ben I. Jan 12 '17 at 15:55
  • Did it - still "#VALUE!". How can I tell where is it pointing? – Tom Picka Jan 12 '17 at 16:01
  • That's very odd. Try just `=ROW()`. Does that work for you? If so, add in elements one at a time. So, `="'"&ROW()`, then `="'"&ROW()&"'!M16"`. – Ben I. Jan 12 '17 at 16:03
  • I simplified my formula slightly, btw. – Ben I. Jan 12 '17 at 16:06
  • I know, that is very odd. =ROW() function works just fine. Anything added does not :( – Tom Picka Jan 12 '17 at 16:23
  • In that case, use a helper column which just contains `=ROW()`. Imagine that you are putting that into column J, starting in `J1`. Now, in K1, use `=INDIRECT("'"&J1&"'!M16")`, and drag down. – Ben I. Jan 12 '17 at 18:07