Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
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 --- -- Cheers, RyanR "Brinto Roy" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
Pls disregard, this won't work, sorry
-- Cheers, RyanR --- pls click yes if I''ve helped you --- "RyanR" wrote: 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 --- -- Cheers, RyanR "Brinto Roy" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
Yeah..it doesnot!
--- Roy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
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 --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
Wow!
Worked like a charm! :) Great help.thanks! Thanks, Roy |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
welcome, glad to hear
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Brinto Roy" wrote in message ... Wow! Worked like a charm! :) Great help.thanks! Thanks, Roy |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
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 a 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use cell reference?
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 --- "Brinto Roy" wrote in message ... 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?? .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |