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

  • Thread starter Thread starter opentalon
  • Start date Start date
O

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
 
Hi,

The A is a worksheet name so

=A!$C$23

returns whatever is in C23 of a worksheet called A

Mike
 
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.
 
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
 
Back
Top