[Solved] Built up cell reference doesn't fill on drag

Discuss the spreadsheet application

[Solved] Built up cell reference doesn't fill on drag

Postby EAIAnalog » Sun Jan 12, 2020 10:56 pm

I'm trying to reference a cell from an external multisheet spreadsheet in a local spreadsheet. Creating the reference using the local spreadsheet's menus results in:

='file:///Directory_name/ss_name.ods'#$'2020'.B44

When I drag the cell handle to the right along the row, I get C44, D44, etc. as I expect.

Now I want to have the sheet name, here 2020, in a local cell and build the cell reference using it. After looking around these forums, I arrive at this construct:

=INDIRECT("'file:///Directory_name/ss_name.ods'#$'"&$A$1&"'."&"B44")

The A1 cell contains 2020, and this works. However, dragging it using the cell handle does not increment to C44, D44, etc. I only get a copy. Is there some nuance that I'm missing?
Last edited by EAIAnalog on Tue Jan 14, 2020 9:05 am, edited 1 time in total.
Open Office 4.1.3 on Mac O/S 10.13.6
EAIAnalog
 
Posts: 3
Joined: Sun Apr 03, 2016 7:16 am

Re: Built up cell reference doesn't fill on drag

Postby MrProgrammer » Mon Jan 13, 2020 4:25 am

EAIAnalog wrote:=INDIRECT("'file:///Directory_name/ss_name.ods'#$'"&$A$1&"'."&"B44")
The A1 cell contains 2020, and this works. However, dragging it using the cell handle does not increment to C44, D44, etc. I only get a copy. Is there some nuance that I'm missing?
Dragging the fill handle copies the formula. Relative cell references in a formula are adjusted when they are copied to other cells. But "B44" in your formula isn't a cell reference; it's text! Use the ADDRESS() function convert a cell reference to text for INDIRECT():
=INDIRECT(ADDRESS(ROW(B44);COLUMN(B44);4;1;SUBSTITUTE("'file:///Directory_name/ss_name.ods'#$'?'";"?";$A$1)))

If it were my spreadsheet I'd store the SUBSTITUTE() function result in a cell and reference it in ADDRESS(). Or I'd store 'file:///Directory_name/ss_name.ods'#$'?' in a cell or defined name and reference it in SUBSTITUTE(). If the sheet name is the same for every row, it's better to build the sheet name (fifth operand of ADDRESS) only once.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Bill and 22 guests