[Solved] Can't use column label 'Quantity' on other sheets

[swdistro]

I need to use column names as the reference in formulas on Sheet2 since the column letters may change depending on how certain export data comes out.

Using the names in a formula on Sheet1 (the sheet the columns exist on) works fine (i.e. =SUM('Quantity') returns the sum of all the numbers in the Quantity column), but calling to the column name on Sheet2 returns Err:508 no matter which way I type it ( =SUM(Sheet1.Quantity), =SUM(Sheet1.'Quantity'), etc). I'm not sure if I'm just typing something wrong or what.

Any help would be appreciated. Thanks.

Edit: Changed subject, was Using Column Name instead of letter not working  Make your post understandable by others  -- MrProgrammer, forum moderator

[robleyd]

=SUM(Quantity) is what you need, from any sheet in the document. The range name definition includes the sheet on which it is contained.

[swdistro]

=SUM(Quantity) is what you need, from any sheet in the document. The range name definition includes the sheet on which it is contained.

This only works on Sheet1 (the sheet the named column is in).

[robleyd]

Perhaps you could elaborate on how you create column names? My answer presumed a named range.

[Lupp]

I need to use column names as the reference in formulas on Sheet2 since the column letters may change depending on how certain export data comes out.

Are you using the option 'Automatically find column and row labels' ?
Are what you refer to as "column names" actually labels inserted into the first row?
This is a dirty way to get partially the functionality of named ranges. It never is usable from a different sheet. Moreover it is only implemented to the full(?) extent in LibO.

My suggetion:
Disable the mentioned option.
Always use explicitly defined named ranges. Using LibO you can even choose the scope of the range definition.
(@robleyd obviously assumed you do it the explicit way.)

[swdistro]

t never is usable from a different sheet.

Ah, okay. So what are my options then if I have to call cells from another sheet where the specific column positions won't be static, but the column headers will be?

i.e. "Title" will always be present as a column header, but in one export it may belong to C1, another export it may be D1 or K1, etc.

[MrProgrammer]

Using the names in a formula on Sheet1 (the sheet the columns exist on) works fine (i.e. =SUM('Quantity') returns the sum of all the numbers in the Quantity column), but calling to the column name on Sheet2 returns Err:508 no matter which way I type it

Automatically find column and row labels is a broken idea from Excel. I've never seen any documentation which shows that column labels from one sheet can be used on another sheet. I never use that feature.

So what are my options then if I have to call cells from another sheet where the specific column positions won't be static, but the column headers will be?

Defined names are global to the spreadsheet, so ensure that the headers in row one are unique. Select the range (headers and data) on Sheet1 and use Insert → Names → Create → Top row → OK. Examine the names you created using Insert → Names → Define. You can now use those defined names on Sheet2. Read about the feature in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum.

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach your spreadsheet.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Lupp]

Ah, okay. So what are my options then if I have to call cells from another sheet where the specific column positions won't be static, but the column headers will be?

References used for the definition of named ranges or named formulas are automatically adapted in the same way as references in cell formulas are.
Check the attached example of a spreadsheet document:

aoo111846_NamedRangesAdaptingReferences.ods

(55.13 KiB) Downloaded 61 times

[keme]

An approach which works "off sheet" and does not require that you define ranges:
Use MATCH() to locate the position of the desired column.
Use OFFSET() to specify a range at that column.
Mockup in attached file.

Edit: Note that INDEX() returns the item number, counting from 1, while OFFSET() uses the input as a displacement of the original position so "keep original" is the same as zero displacement. This is the reason for the "minus one" in the formula. It is not uncommon to encounter this kind of 1-base vs zero-base situation with numbering/counting in the digital realm.

[swdistro]

An approach which works "off sheet" and does not require that you define ranges:
Use MATCH() to locate the position of the desired column.
Use OFFSET() to specify a range at that column.
Mockup in attached file.

Thank you kindly!