Log in

No account? Create an account
08 June 2012 @ 10:15 am
Excel question  
I have exactly one Excel macro that I use on a daily basis. It's in my writing tracking spreadsheet and it summarizes the current status of all my submissions. Each story is tracked on its own worksheet (tab) with one row per submission; the macro copies all rows with a non-empty status (column C) from every other sheet to the summary sheet, like this:


Is there any way to do this without a macro?

Bonus question: Can you tell me how to do this in Numbers (Apple's iWork spreadsheet)?
Ulrikaakirlu on June 8th, 2012 06:02 pm (UTC)
No answers for you but *icon love* anyway.
triskelmoon: nerdtriskelmoon on June 8th, 2012 11:13 pm (UTC)
Have you tried just linking the cells via formula? Though it would require a new hand entry with each new tab added.

example: Summary sheet B2: =Sheet1!A1
and so on.

You can also input = in the cell and then just click the destination cell and it will autopopulate that cell. Then you could drag the cell to relationally fill the further cells.

If you want to do it for only non empty cells, you'd add an IF function. =IF(Sheet1!A1=null, 0, Sheet1!A1)

But then you would have empty fields (unless this is a financial sum spreadsheet, then we could use SUM functions too).

Caveat: this is entirely Excel based and I have no idea if it would work in Numbers.
David D. Levinedavidlevine on June 9th, 2012 12:28 am (UTC)
I add a new row to each sheet when I submit that story. So I would have to manually add those links to the summary sheet every time I add a row, not just every time I add a sheet. Sorry!
billeyler: work Popejoy accountantbilleyler on June 9th, 2012 12:00 am (UTC)
This sounds like something you can use grouping for, but I'd have to see the spreadsheet to be sure.
David D. Levinedavidlevine on June 9th, 2012 12:37 am (UTC)
I've emailed you a copy. Thanks!
scarlettina: Geek Crossingscarlettina on June 9th, 2012 03:27 am (UTC)
You are a geek. It's just that simple. Really. I have nothing else to say. Continue whatever it was you were doing. That is all.
abqdan on June 9th, 2012 04:00 pm (UTC)
My simple approach would be to program this in PHP/MySQL instead as a complete web-based tracking application, requiring several hundred hours of application development time... because I'm a geek!

I'm wondering though... since it works, why do you want to change it?
David D. Levinedavidlevine on June 9th, 2012 04:12 pm (UTC)
Because it takes a long time for the macro to run, and because I'm considering moving to something other than Excel.
et in Arcadia egoboo: Ada  Lovelaceapostle_of_eris on June 10th, 2012 12:47 am (UTC)
I'm pretty sure my Excel macros could be significantly improved by learning VBA, but the gain isn't enough for the pain.