creating the pattern C7,C14,C21...

slaygay · #1 07-03-2009, 02:09 PM

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.

martindwilson · #2 07-03-2009, 03:51 PM

try
=INDIRECT("C"&ROWS($A$1:A1)*7)

JBeaucaire · #3 07-03-2009, 03:54 PM

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)

martindwilson · #4 07-03-2009, 06:39 PM

deleted duplicating my own post lol

slaygay · #5 07-04-2009, 12:27 PM

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" .

Ancalagon12321 · #6 07-04-2009, 01:26 PM

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:

Code:

Sub EnterValueAsFormula()
    Dim rng As Range: Set rng = Selection
    rng.Formula = rng.Value2
End Sub

shg · #7 07-04-2009, 01:41 PM

Quote:

I never find it less than shocking that MS neglected to build this functionality into Excel directly

Copy > Paste Special, Values.

Ancalagon12321 · #8 07-04-2009, 02:13 PM

@shg:

Doing a paste-as-values doesn't solve this person's problem, whereas my macro does.

shg · #9 07-04-2009, 02:47 PM

Sorry, I can't tell what your procedure is intended to do. It's the same as

Code:

Sub x()
    Selection.Value2 = Selection.Value2
End Sub

Ancalagon12321 · #10 07-04-2009, 03:54 PM

@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.

shg · #11 07-04-2009, 04:57 PM

Different in what way?

Ancalagon12321 · #12 07-04-2009, 05:23 PM

@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.

JBeaucaire · #13 07-04-2009, 08:24 PM

Quote:

Originally Posted by Ancalagon12321

@shg: But notice, in any case, that my macro (and yours) are different from just doing a paste-as-values.

Quote:

@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.

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.