How to use cell reference?

[Brinto Roy]

Hi,

I need to fill a table using cell reference as below,by drag n fill
method:

Source Required Table
A B C D E
A1 A1 A2 A3 A4
A2 A5 A6 A7 A8
A3 A9 A10 A11 A12
A4 … … … ..
A5
….
…
Any clue how to go for it?

Thanks,
Roy.

 

[RyanR]

My approach would be as follows....

Assumption, the result table will 4 columns, no more, no less....

In B1 type "=A1", in B2 type "=A5" without the quote marks.
Select B1:B2, and drag across to column E (to copy the formula)
Then select rows 1:2, and drag down as far as you need to fill the rows.

--- pls click yes if I''ve helped you ---

 

[RyanR]

Pls disregard, this won't work, sorry

 

[Brinto Roy]

Hi Ryan,

Thanks for the prompy reply.
But the solution that you provided does not seem to solve the problem :
(
After I select B1:B2,and drag across to column E..the column name even
changes,i.e A1 changes to B1,C1...which i dont want.
even tried with "=$A1" to keep the column name constant..But even
thats not helping!

Thanks,
Roy

 

[Brinto Roy]

Yeah..it doesnot!

--- Roy

 

[Max]

This seems ok ..
In B1: =OFFSET($A$1,ROWS($1:1)*4+COLUMNS($A:A)-5,)
Copy B1 across to E1, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Brinto Roy]

Wow!
Worked like a charm!
Great help.thanks!

Thanks,
Roy

 

[Max]

welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Brinto Roy]

Well, though the OFFSET function is working fine,I am not being able
to use ROWS and COLUMNS,as they are not supported by Xcelssius.
How can i modify the function B1:=OFFSET($A$1,ROWS($1:1)*4+COLUMNS
($A:A)-5,) without using ROWS and COLUMNS,but with the same
functionality??

The Excel functions currently supported by Xcelsius are: ABS
ACOS
ACOSH
AND
ASIN
ASINH
ASSIGN
ATAN
ATAN2
ATANH
AVEDEV
AVERAGE
AVERAGEA
BETADIST
CEILING
CHOOSE
COMBIN
CONCATENATE
COS
COSH
COUNT
COUNTA
COUNTIF
DATE
DATEVALUE
DAVERAGE
DAY
DAYS360
DB
DCOUNT
DCOUNTA
DDB
DEGREES
DEVSQ
DGET
DIVIDE
DMAX
DMIN
DOLLAR
DPRODUCT
DSSTDEVP
DSTDEV
DSUM
DVAR
DVARP
EDATE
EOMONTH
EVEN
EXACT
EXP
EXPONDIST
FACT
FALSE
FIND
FISHER
FISHERINV
FIXED
FLOOR
FORECAST
FV
GE
GEOMEAN
GT
HARMEAN
HLOOKUP
HOUR
IF
INDEX
INT
INTERCEPT
IPMT
IRR
ISBLANK
ISNA
ISNUMBER
KURT
LARGE
LE
LEFT
LEN
LN
LOG
LOG10
LOOKUP
LOWER
MATCH
MAX
MEDIAN
MID
MIN
MINUS
MINUTE
MIRR
MOD
MODE
MONTH
N
NE
NETWORKDAYS
NORMDIST
NORMINV
NORMSINV
NOT
NOW
NPER
NPV
OFFSET
OR
PI
PMT
POWER
PPMT
PRODUCT
PV
QUOTIENT
RADIANS
RAND
RANGE_COLON
RANK
RATE
REPLACE
REPT
RIGHT
ROUND
ROUNDDOWN
ROUNDUP
SECOND
SIGN
SIN
SINH
SLN
SMALL
SQRT
STANDARDIZE
STDEV
SUM
SUMIF
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
SYD
TAN
TANH
TEXT
TIME
TIMEVALUE
TODAY
TRUE
TRUNC
UPPER
VALUE
VAR
VDB
VLOOKUP
WEEKDAY
WEEKNUM
WORKDAY
YEAR
YEARFRAC


--- Roy

 

[Max]

Can't think of any. Perhaps a workaround could be to generate the numerical
results returned by the ROWS and COLUMNS bit in Excel, then copy/paste
special as values (static) for use as helpers into Xcelsius.

In Excel, you could put in any cell: =ROWS($1:1)*4+COLUMNS($A:A)-5
Copy that across by 4 cols, fill down as far as required.
Then copy that 4 col range n paste special as values into H1:K1 down
in the Xcelsius sheet.

Then you could place in B1: =OFFSET($A$1,H1,)
and copy across to E1, fill down to get the same end results.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik