Jayne ,'The Message'
Got a question about technology? Ask it here. Discussion of hardware, software, TiVos, multi-region DVDs, Windows, Macs, LINUX, hand-helds, iPods, anything tech related. Better than any helpdesk!
I have a complicated Excel question.
I have a multi-tabbed spreadsheet. Each tab is sorted by archive and has a subtotal indicating how many seconds from that archive we've sold. What I want is a summary sheet which adds up all the subtotals.
Is there a formula which will look for "News Subtotal" in each tab and then take the number in the cell next to it? I feel like SUMIF should do this but I don't know how it works.
Depending on how your spreadsheets are set-up, what about vlookup? Or hlookup is it is a horizontal field.
Does the cell #/location not stay the same in each tab (ie, does Tab1 move the News Subtotal from H100 to H101 depending on what's going on), and that's why you need it to be next to the "News Subtotal"?
Yeah, each tab has a completely different number of rows, and in some cases columns. So what I need is to take all of the subtotal rows and transfer them to a clean sheet so I can add up those values.
Sorry, I mean when the spreadsheet is updated, does the placement of the value change within the tab? Not that in Tab1 it's H100 and Tab2 it's G75, but like, on Monday Tab1 is H100 and Friday it's Tab1 H106?
Because if it's just in a different place in each tab, that's easy enough. If it changes where it is, different task.
Oh I see. I'm working with a static copy so no, the row will not change.
Oh, sure, that's easy then--just pick the sheet where you want it to add up, type the equals sign, then go click on the cells you want it to add, it'll be like =Sheet2!B7+Sheet3!C14
(I mean, that's a more manual way than it finding all the cells next to a certain phrase, so maybe if you've got 100 sheets it doesn't work so well?)
(I mean, that's a more manual way than it finding all the cells next to a certain phrase, so maybe if you've got 100 sheets it doesn't work so well?)
Yeah, that's the process I'm trying to avoid. I don't want to spend half an hour copy-pasting.
Yeah, I'd do a IFERROR(VLOOKUP(TAB1ARRAY,NEWS SUBTOTAL,[correct column],false),0)+ IFERROR(VLOOKUP(TAB2ARRAY,NEWS SUBTOTAL,[correct column],false),0) etc. on the sheet you want the subotals to be. Or one of those one a line if you want to list all the subtotals one after the other, I guess, maybe you wouldn't need the IFERROR then.
sumi,
Le nubian - it turns out that the university library has an e-book and a physical book that my boss can use.
good.