excel cell has =A!$AS$1:$AY$20. What is the ! and formula doing?

[opentalon]

I received a spreadsheet that includes a list of formaulas that I do not
understand. The person that sent it, did not create it and was unaware the
codes existed. What do these cell do and what are they referencing?
=A!$B$1:$I$6
=A!$C$25
=A!$A$1:$M$55
=A!$C$23
=A!$E$23

 

[Mike H]

Hi,

The A is a worksheet name so

=A!$C$23

returns whatever is in C23 of a worksheet called A

Mike

 

[~L]

Somewhere in this workbook is a sheet called A. A! refers to the sheet
titled 'A'

All of these are absolute references within that sheet.

Absolute references, denoted by placing the $ before the column or row are
references that do not update as the formula is moved. $A1 when moved will
update only the row portion (the 1 will become 2, 3, 4, etc). A$1 will only
update the column portion (A1, B1, C1, etc). $A$1 will not update row or
column when moved.

The : between two reference points indicates a range that includes all cells
that would be included if you clicked and dragged your mouse between those
two points on the spreadsheet.

 

[Max]

From the looks of it, these are probably defined/named ranges.

In a new sheet, click Insert > Name > Paste > PasteList
You should then see something like this:
MyR : =A!$B$1:$I$6
MyList : =A!$C$25

MyR : =A!$B$1:$I$6
means the defined range's name is MyR
and it refers to the range B1:I6 in the sheet named: A

Similarly
MyList : =A!$C$25
means the defined range's name is MyList
and it refers to the range C25 in the sheet named: A
(a range can be just a single cell)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik