Excel is able to create 'C7', 'C14', 'C21'....C(n*7)
But what i am trying to input is '=C7', '=C14, '=C21' where i am trying to copy the figures from the above mentioned cells in order to summarise my data.
What actually is happening is i created similar tables with and the information i want is 7 rows apart.
Would appreciate if someone helps me out. I cant get why excel is able to do recognise the logic for the first line but not with the '=' sign.
Please help this noobie. THanks.
try
=INDIRECT("C"&ROWS($A$1:A1)*7)
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Well, INDIRECT() let's you build up a cell reference in pieces like this. Just be careful, if you put 100s and 100s of these on a sheet the sheet will start to calculate slower.
In row 1 somewhere this would work:
=INDIRECT("C" & ROW()*7)
If you start in a different row, you need to convert the first ROW() answer back to 1. So if your first formula is in row 6, it would need to be ROW()-5:
=INDIRECT("C" & (ROW()-5)*7)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
deleted duplicating my own post lol
Last edited by martindwilson; 07-03-2009 at 07:41 PM.
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
thanks man!
This indirect function is something new for me which i guess should be rather useful.
Anyway, another method which i managed to come out with as a workaround is to use the c7,c14,c21 pattern and perform a find and replace from "c" to "=c" .
The best method here is first of all to make sure that you have access to a macro that 'enters' the values of cells as their formulas. E.g. The following will do the trick:
(N.B. I never find it less than shocking that MS neglected to build this functionality into Excel directly, considering how often I use it.)Code:Sub EnterValueAsFormula() Dim rng As Range: Set rng = Selection rng.Formula = rng.Value2 End Sub
Then you can use a formula like
="=C" & COLUMN()*7,
or whatever, and then highlight the range and run your macro on it.
Copy > Paste Special, Values.I never find it less than shocking that MS neglected to build this functionality into Excel directly
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
@shg:
Doing a paste-as-values doesn't solve this person's problem, whereas my macro does.
Sorry, I can't tell what your procedure is intended to do. It's the same as
... is it not?Code:Sub x() Selection.Value2 = Selection.Value2 End Sub
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
@shg:
Yes, I think setting the Value (or Value2) property of a range has the same effect as setting its Formula property (which can actually be very annoying if you want a cell to contain the value "=whatever").
But notice, in any case, that my macro (and yours) are different from just doing a paste-as-values.
Different in what way?
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
@shg:
Suppose a cell contains the text string "=A1".
If you copy and overwrite using a paste-as-values then afterwards the cell still contains the text string "=A1".
On the other hand, if you use your macro then afterwards, the cell contains the formula "=A1" and its value is whatever is contained in the cell A1.
Originally Posted by Ancalagon12321
I don't see how you reach that conclusion. Just because you're not actually using the commands copy-pastespecial-values... your macros both do that same thing. I use it all the time, and the distinction in method isn't worth debating... it is what it is.@shg: ...if you use your macro then afterwards, the cell contains the formula "=A1" and its value is whatever is contained in the cell A1.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
if i put =a1 in a cell formatted text
then copy/paste special values nothing changes!
but if i run tht macro on it it does.
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
JBeaucaire:
Having patiently explained how the two things are different, I can only ask you to please read my post again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks