[Question] Excel Fill Question (Or, Help Me Do My Job!)

Status
Not open for further replies.
Chad Sexington

Chad Sexington

I inherited a spreadsheet from the last project coordinator that has some inefficiencies I'd like to ameliorate.

What I'd like to do is take a value from one sheet in the same book, and display it in this inherited sheet. I can get that far. My problem is filling it across the row for all dates.

So what I have is a list of names going down, a list of dates going across. What I want is for the first date to call up
=sum(sheet2!A3:A4); the second date to call up =sum(sheet2!A3:A5); the third date to sum with A6, and so on, with only the last number changing.

What happens, however, when I fill across, is that the LETTER value changes, so I get =sum(sheet2!B3:B4), =sum(sheet2!C3:C4)

I am struggling to figure out how to prevent this and make it do what I'd like.

I think the crux of the problem is that the sheet I am calling from has the names going across and the dates going down; the opposite of the sheet I am working on. I could recreate either sheet, but it's quite a bit of work and each one is better for its purpose constructed as they are.

I have a second, much more minor, issue but I'll leave it there for now. Thanks in advance.
 
MindDetective

MindDetective

First of all: do you know about anchoring rows/columns with the $ symbol? Example: sum(sheet2!$A$3:A4)

That won't actually solve anything for you. Secondly, you temporarily create your new array so the dates are vertical, then move them (paste special might work?) into their proper positions.

Is this list going to grow as more dates are added? It might actually be easier to recreate the sheet you are reading from...
 
strawman

strawman

Congratulations, you've entered the annoying territory of excel!

Minddetective's workaround is probably your best bet. After that it gets terribly annoying.
 
Chad Sexington

Chad Sexington

I opted to create a whole new sheet. The one I inherited was a cluttered mess anyway, so I've redesigned it, and when I'm done it will, hopefully, be beautiful. And more importantly, functional. But thanks for your help.

My second (and minor) question, which is wholly aesthetic, is if there is a way to tell a cell not to display a value for its formula (sum:blahblahblah) if it is the same as the previous value.
 
Chad Sexington

Chad Sexington

ncts_dodge_man said:
If it's a nearby cell, you can use the If statement.

Code: 
=if(sum(a3:a5)=b2,"",sum(a3:a5))
Click to expand...
Hm, I get an error. It says something's up with the ,"",sum part.[DOUBLEPOST=1366049337][/DOUBLEPOST]Nevermind. Figured it out.[DOUBLEPOST=1366049408][/DOUBLEPOST]You guys are awesome. Thanks a ton :D
 
evilmike

evilmike

For future reference,

You could use the OFFSET function to do create the formula you originally needed:

=sum(OFFSET(sheet2!$A$3,0,0,COLUMN(),1))

Note that it is set up to start in column "B". You can adjust this position by adding or subtracting from the COLUMN() result.
 
Status
Not open for further replies.
Top