Excel help needed!

July 1, 2011 7:58 AM

I have a formula for inputting into my expense sheet but it doesn't populate the field like it should. I stole the formula from my time sheet, and that does populat the column for the 4 weeks after I manually put in date 1. It is: =IF($B$4=0,"",SUM(B4+1)) So...why does it work in one sheet but not the next?

I know I do need to adjust it for the structure of the new sheet, to make it work for column B cell 12. Does that help?

BTW I am a complete noob in spreadsheets.

Cheers

posted by dash_slot- to computers & internet (7 answers total)

Is it possible that the B column in your new spreadsheet is not formatted in the same way as in the original spreadsheet? Check formatting for the column and make sure it's numeric.
posted by lulu68 at 8:09 AM on July 1, 2011

Is the value in B set for zero or null? Check the cell format in the two sheets to see if they're the same. Also try it without each of the "$".
posted by ldthomps at 8:43 AM on July 1, 2011

If your "date 1" is in cell B12, then you need to replace "$B$4" with "$B$12".

Presumably, the subsequent dates would be B13, B14, etc. So you'd replace "SUM(B4+1)" with "SUM(B12+1)" for cell B13, "SUM(B13+1)" for B14, etc.

Also, you don't need to write "SUM(...)". That is a function used to add up multiple cells. Since you're just trying to add 1 day to the value from a single cell, you can replace "SUM(B4+1)" with "B4+1" to get the same result.
posted by hammurderer at 8:47 AM on July 1, 2011

lulu68, I dont know how to check :(

ldthomps: ditto.

hammurderer: i) my colleague helped me w that & ensured it was altered. ii) Ill try removing iSUM, t may help eh?
posted by dash_slot- at 9:45 AM on July 1, 2011

Highlight column B by clicking once in the top 'box' in the column (the one that says 'B'). Then, with the cursor in the same place ie somewhere in the highlighted column, right-click and select 'Format cells...'. You will see several tabs. Choose the 'Number' tab, and you will see on the left side a list of possible formats. If you are working with dates, then choose 'Date' and you will see a list of possible date formats on the right. Choose the one that represents how your dates are formatted.

Now, try your formula again.

As ldthomps suggested, before you start with the new spreadsheet, go to the original spreadsheet and check what formats are used in the relevant columns using the process above. Then use these formats when setting up your new spreadsheet.

ps: I'm a n00b as well --- so hope this makes sense!
posted by lulu68 at 11:06 AM on July 1, 2011

Your statement about this populating a column, rather than a cell / field: are you expecting this to populate multiple cells? If so then try entering it as an array formula. To do that just hold down SHIFT and CTRL at the same time as hitting enter when you are parked on the formula.

Also, you should be able to tell if the original is an array formula since it will be enclosed within {}.

More likely though is that you just need to drag the formula down to all the other cells.
posted by NailsTheCat at 1:53 PM on July 1, 2011

This is one of those things where someone who knows Excel could poke around for a minute and figure it out, but it would take forever to detail every little step and possibility over the internet. Based on your responses here, you're REALLY a noob (and I mean that in the friendliest possible way!), so it might be best to just take the whole thing to someone in person and say "please make this work." You'll save yourself some headaches...
posted by ella wren at 4:51 PM on July 1, 2011 [1 favorite]