8

I hava a cell which is b30 in sheet sheet-alpha, I want get reference as string into clipboard, literally I want my clipboard contains string 'sheet-alpha'!B30

How do I do that in google sheet?

jay.m
  • 1,618
  • 3
  • 16
  • 28
  • Look at the answer by Dannid in [this question][http://stackoverflow.com/questions/26522305/google-sheets-how-do-you-get-the-formula-from-a-cell-instead-of-the-value]. You have to use a custom script – daniel Jan 31 '16 at 04:50
  • This question is about how to use Google Sheets, not about programming, so it's off-topic in [so]. See [help/on-topic]. – Rubén Feb 19 '16 at 00:24
  • @Rubén, I fail to see that. I came here trying to easily copy the cell reference (which simply contains a number--no formula) so that I could past it into the find box to see if that cell is used in a formula somewhere. (It seems jay.m edited his Q to make it clearer so maybe that was the problem.) – aamarks Jun 14 '20 at 18:46
  • @aamarks I suggest you to post a question on [webapps.se] – Rubén Jun 14 '20 at 23:54
  • @Rubén, Ah sorry. I see now that the question wasn't posted in a group appropriate for Sheets. – aamarks Jun 15 '20 at 13:22

2 Answers2

1

One solution to your need can be achieved in 2 steps:

First, open [top menu] Extensions > Apps Script and copy&paste the following function:

function getSheetName() {
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return sheetName;
}

The above code enables you to call the sheet name from wherever you need it by using the formula =getSheetName() in any cell.

Then, add another column to the right of column B (new Column C) and copy&paste the following array formula at the top most cell:

={"Ref 2 Str"; ARRAYFORMULA(IF(B2:B="","","'"&getSheetName()&"'!B:"&INDEX(ROW(INDIRECT("A"&(ROW()+1)&":A")))))}

The above function looks for non-empty cells on column B and for each non-empty cell it puts in column C the string reference for the corresponding cell in column B (in the format you need).

Elad Ratson
  • 706
  • 1
  • 7
  • 23
0

Pressing

Ctrl `

(the backtick under the tilde, top left of most keyboards) will toggle between showing or hiding the formulas in your worksheet. When the formula is showing you can copy in the normal way with Ctrl C.

Ben Collins
  • 321
  • 1
  • 2
  • 9
  • 3
    Thanks for your answer. My questions is how to put 'questions for configuration'!B30' into the clipboard, the string in the clipboard should be 'questions for configuration'!B30', not the content of it. – jay.m Feb 18 '16 at 14:22
  • @jaym, the answer is correct. If you select the **View** menu, then select **All formulas**, the spreadsheet will only show the original formulas, not the computed content. Then, when you do Ctrl+C, the formula will be copied to the clipboard. – daniel Feb 18 '16 at 14:48
  • 3
    My origin question might be too long and ill stated. I simplified it,hope it helps to clear my question. The answer does not answer my question. – jay.m Feb 20 '16 at 01:58
  • This still does not give you the sheet. – mathtick Jun 06 '21 at 16:40
  • even with the formula showing, it still does not copy the formula – tuexss Feb 22 '23 at 09:55