[Solved] Referencing cells in different sheet

Discuss the spreadsheet application

[Solved] Referencing cells in different sheet

Postby hengis » Mon Dec 16, 2019 11:32 pm

Hi I am having problems referencing cells.. I create daily a new sheet and do some calculations on it. I then copy a new line on my master sheet(same workbook) but the links do not update. eg
line 30 Sheet50!$C$33
line 31 Sheet51!$C$33
line 32 Sheet52!$C$33

When I copy say line 32 into line 33 the sheet number does not update or increment to Sheet 53

Any suggestions will be much appreciated
Last edited by Hagar Delest on Tue Dec 17, 2019 11:06 pm, edited 1 time in total.
Reason: tagged solved
WINDOWS 8.1 OPEN OFFICE 4.1.2
hengis
 
Posts: 15
Joined: Sat Jan 02, 2016 11:43 am

Re: referencing cells in different sheet but same workbook

Postby Lupp » Tue Dec 17, 2019 12:04 am

If you copy a relative (without a $ sign in front) sheet reference to a different sheet, it will adapt. Pasting it into the same sheet you give no reason for adapting. Relative row and column references will adapt.

Anyway: You are using Excel syntax. The separator between the sheet part of an address and the rest is the dot (point, full stop, period) in Calc. The exclamation mark is not valid.

Concerning your attempt to access a sequence of sheets from a sequence of rows: There are no standard means in Calc to do so. You need to either adapt the sheet names manually or to resort to user code (a "macro" function).

If the names of your sheets are as sytematically chosen (as it seems) with subsequent numbers at the end, you can, however, create a list of the sheet names separately in a helper column by dragging down with the mouse, and then reference the sheets in a series of rows with the help of the ADDRESS() function using the optional shetname parameter, placing the ADDRESS call inside an INDIRECT() call in every case.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2659
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: referencing cells in different sheet but same workbook

Postby hengis » Tue Dec 17, 2019 1:47 pm

A BIG THANKS. I would have replied earlier but I have had to get my head round the indirect function. My brain now nips and I will have to lie down in a darkened room. Once again a BIG THANKS
WINDOWS 8.1 OPEN OFFICE 4.1.2
hengis
 
Posts: 15
Joined: Sat Jan 02, 2016 11:43 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests