• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formulas with variables [SOLVED]

G

glennpc

Member
I have a spreadsheet with a Summary page and a Details page. I need formulas in the summary page that get every 30th value in the same column in the Details page and put them in adjacent rows of the same column on the Summary page.


Details page has 100 in cell C27, 200 in cell C57, 300 in cell 87, etc.


For the Summary Page, if I want these values in Column B, starting in B2


=Details!$C27 gives me 100 in cell B2


=Details!$C57 gives me 200 in cell B3


etc.


Its going to take forever to type all these formulas in. I can DRAG them to copy them. But it increments the row by 1 and I need it to increment the row by 30. How can I do this? If I highlight 2 cells and then do the drag, this doesn't work like a normal copy. My client doesn't want a macro solution, she wants a formula solution.
 
Try this:

=OFFSET(Details!$C27,(ROW($A1)-1)*30)


The ROW function is going to act as a counter, starting at 1 then to 2, 3, etc.

So, we'll first look at C27 with no offset. Then, C27 with an offset of 30 rows (aka, C57). Then, C27 with offset of 60 rows (aka, C87). Copy down this formula as far as needed.
 
I entered that, and it keeps telling me I've entered too few arguments for this function.


Also, would I enter that as the first value I want on my summary page and copy down from there? Or do I use a different formula for the first value, like =Details!$C27 ?
 
Hi, glennpc!

Try adding a comma before last parenthesis:

=OFFSET(Details!C$27,(ROW($A1)-1)*30,)

Or use this another approach:

=INDIRECTO(DIRECCION((FILA()-2)*30+27;3;4;1;"Details")) -----> in english: =INDIRECT(ADDRESS((ROW()-2)*30+27,3,4,1,"Details"))

Regards!

PS: Add and embracing IF(<expression>="","",....) for avoiding zeros instead of blank values.


EDITED


PS2: And changed fixed row instead of column in Luke M's version, thanks to jeffreyweir catch.
 
Sorry, missed a comma. formula should be:

=OFFSET(Details!$C27,(ROW($A1)-1)*30,0)


This formula will give you the value in C27, so it's the first one. Copy from there. There is no offset at the beginning due to ROW(A1)-1 evaluating to 0. =)
 
@Luke M

Hi!

Almost 3' late... I think you're needing a Carlsberg, join me!

Regards!

PS: If had one yet, well, then you need a six-pack :p
 
@SirJB7

Indeed my friend, my head is getting weary...time to take off for the evening.
 
Can I make a suggestion, that possibly might raise some ire? And please don't take this as criticism of your efforts - I think you ninjas are awesome in that you've given up your time to make nearly 9000 posts between you.


Here goes:


I know you guys know the difference between using Volatile functions and non Volatile.


And I know that this volatile/non volatile issue only matters if users are going to have long calculation chains with a volatile formula at the head of it.


But I also believe that prevention is better than a cure, and that even if the issue doesn't affect the current spreadsheet an op is working with, sooner or later, they're likely to build something larger while drawing on their working knowledge of INDIRECT, OFFSET etc gleaned from forums such as this.


With that in mind, wouldn't best practice be to:

1. at the least warn users every time that we suggest a volatile function that they may cause problems in some circumstances , and

2. at best , strive to provide providing non-volatile function combinations in place of volatile when such an alternative is available?


For instance, in this case: =INDEX(Details!C:C,ROW(Details!$C$27)+(ROW()-1)*30) is a non-volatile alternative.


In fact, I've only ever seen one thing that OFFSET can do that INDEX can't. And that one thing is very rarely used in practice.


Your thoughts?
 
@jeffreyweir

Hi!


Why that would possibly raise some ire if you're right? I don't think anybody, Ninja, member or KeyMaster, would fly off the handle if it's an useful improvement.


But I'd only consider (and I think that I actually do) applying those non-volatile formulas when and if they're not more complex or in cases when the involved data implies a slower performance noticeable for the user, not just merely because of the fact of providing a state-of-the-art formula which it'd be more difficult to understand. I'd always try to make things easier to the users with a balance between efficiency, best practices and practicality.


Said this, my thought about your very interesting and enriching post is: keep in mind.


Regards!
 
"But I'd only consider (and I think that I actually do) applying those non-volatile formulas when and if they're not more complex or in cases when the involved data implies a slower performance noticeable for the user, not just merely because of the fact of providing a state-of-the-art formula which it'd be more difficult to understand. I'd always try to make things easier to the users with a balance between efficiency, best practices and practicality."


Well said, SirJB7.


Only reason I mention this is that for every INDEX I see 20 OFFSETs. But your reply helps me to remember the reason for this...OFFSET is probably easier for the average user to wrap their head around.
 
@jeffreyweir

Hi!


I almost forget about it. As your amazing number and quality of your contributions make this not a must, I'd too want to welcome you to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


BTW, why don't taking a breath and passing by the Introduce yourself topic? It'd be nice if the community could know something more about such a promising contributor, and I include myself among them.


Regards!
 
...and the reason I thought this might raise some ire is that it does smack of a chandooforum newbie (me) sticking his oar in.
 
@jeffreyweir

Hi!

After following my kind and humble suggestion of my previous comment, I'd invite you click on my nick at any comment (if you haven't done it yet) and check Interests. Now speaking only for myself that should fully answer the ire issue, doesn't it?

Regards!
 
Guys-- appreciate all this effort on your part! I've tried the various suggestions, and although none work perfectly, the one that comes the closest is the one from SirJB7 that looks like this:


=INDIRECT(ADDRESS((ROW()-2)*30+27,3,4,1,"Details"))


The only thing wrong with this one is that it is giving me in the very first cell what should be in the second cell, and so on. In other words, the first use of the formula is not giving me what is in C27 on the Details page-- it is giving me what is in C57 on the details page. Was I supposed to start the formula in the second line of my summary page? I guess that would work-- I could use just =Details!$C$27 for the first summary item. Is that what you intended?
 
I just tried what I suggested-- just using =Details!$C$27 for the first summary item and then the formula for the second (and copied down to the rest). Still doesn't work. The top one is fine, but it leaves out the SECOND summary value now-- uses the third summary value in the second position. Always off by one.
 
Hi glennpc,


Assuming details as in your first post kindly try this formula in Summary sheet in Cell B2, Press Ctrl+Shift+Enter and drag down:

[pre]
Code: 
=OFFSET(Details!$C$1,SMALL(IF(ISBLANK(Details!$C$2:$C$87)=FALSE,ROW($C$2:$C$87)),ROW(A1))-1,0)
[/pre]
Let me know if it doesn't work i will upload a sample file.


Regards,

Faseeh
 
THIS one seems to work. Going back to Luke's suggestion:


=OFFSET('June 13'!C$27,(ROW($A1)-1)*30,0)


This is a corrected version. Luke originally had $C27-- JeffreyWeir suggested that the reference had to be absolute ($C$27). Luke replied "just the row, not the column" and he gave the correction as C$27 which is what I'm using.


C$27 works and $C27 does not.


I'm pretty sure this is the solution. Thanks guys!
 
You must log in or register to reply here.
Back
Top