Making a cell reference with data in other cells

English
#1

Hello,

I have two calc sheets in the same spreadsheet and would like to draw data from one to the other. The column and row references change.

Using the ROW command I have put on sheet1 the row number from sheet 2.
So I now have 100 items with the row number attached.

A column reference is also added on sheet1.

I would like to now tell sheet one to collect data from the columns and the rows that have been allocated.

i.e ‘sheet2’.&J2&J3 - J2 containing the column and J3 containing the row number generated by the ROW command. These are on sheet1.

Looking at the forums, I have tried a few things but to no avail. Keep getting a REF! error. Can anyone help please.

Kind Regards

#2

Maybe try INDIRECT(‘sheet2’.&J2&J3)

#3

Hi, Thank you for taking a look. I have seen INDIRECT on my searches. What I have tried either throws up REF! or Error. As written here seems to be Err:509. I can get it working to pull data on the same sheet but not to pull from sheet2.

#4

Sorry, I was on my phone and it isn’t always easy to see what’s there, you need double quotation marks that haven’t been converted to curly quotes, try =INDIRECT("$Sheet2."&J2&J3)

1 Like
#5

My understanding of INDIRECT from what I have read in the forums and from your suggestions is that you make a formula out of text in " " and cell references.

It did not work on my spreadsheet, so I came and asked. Your suggestion was the same as I had read but still does not work on my spreadsheet.

I have now tried it on a new blank spreadsheet and it worked so it obviously is correct. I will have to have a play. Thank you very much for your time.

#6

Error 508 pair missing, possibly only one quote? See Error Codes in LibreOffice Calc

#7

Have now copy and pasted data to new spreadsheet and it is now working.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.