Excel IF formula or something easier?
November 21, 2019 12:04 PM Subscribe
I can't seem to auto-fill this formula and there must be an easier way.
I have a workbook with multiple tabs that all have the same info in each sheet's cell B5 (the date someone did a thing). Each sheet also has the person's name in B1. I want a "cover sheet" that will pull only the names of people who didn't do the thing in B5 yet.
I wrote an "if SheetX B5 is blank, return B1" formula, but it won't copy correctly into the downward cells - it keeps trying to move the cells over to B6 or B2, etc.
I feel like this has a simple solution. Do I need a different formula or am I autofilling wrong?
I have a workbook with multiple tabs that all have the same info in each sheet's cell B5 (the date someone did a thing). Each sheet also has the person's name in B1. I want a "cover sheet" that will pull only the names of people who didn't do the thing in B5 yet.
I wrote an "if SheetX B5 is blank, return B1" formula, but it won't copy correctly into the downward cells - it keeps trying to move the cells over to B6 or B2, etc.
I feel like this has a simple solution. Do I need a different formula or am I autofilling wrong?
Response by poster: Ahhh! That works, but it still doesn't want to auto-complete each sheet - it just gives me a row of data from the same sheet.
posted by nakedmolerats at 12:21 PM on November 21, 2019
posted by nakedmolerats at 12:21 PM on November 21, 2019
Auto fill doesn't work for different sheets. You need a list of sheets in your workbook to refer to in your formulas.
This page explains how to get them.
posted by soelo at 12:26 PM on November 21, 2019 [2 favorites]
This page explains how to get them.
posted by soelo at 12:26 PM on November 21, 2019 [2 favorites]
Trying to visually see this but you can do something "CountIf" and if the column is zero, that indicates it's blank.
posted by hillabeans at 12:58 PM on November 21, 2019
posted by hillabeans at 12:58 PM on November 21, 2019
Response by poster: hillabeans, I thought about that but still not sure it solves the problem of auto-pulling the same cell from multiple sheets.
posted by nakedmolerats at 1:07 PM on November 21, 2019
posted by nakedmolerats at 1:07 PM on November 21, 2019
Can you do: "if SheetX $B$5 is blank, return $B$1"? I think that would solve it.
posted by hillabeans at 1:21 PM on November 21, 2019
posted by hillabeans at 1:21 PM on November 21, 2019
Assuming you just want the cover sheet to show a name if the person has failed to complete the task in any of the sheets, as opposed to which sheet is missing a date, you could probably use IF but use OR within that for the logical test.
So for example I just pulled up a new Excel file with three sheets - cover, sheet 2 and sheet 3. Sheets 2 and 3 had a names in the first two rows of column A, sheet 2 had a date for both names in column B and sheet 3 had no date for name2.
On the cover sheet I used this formula
=IF(OR(Sheet2!B1=0;Sheet3!B1=0);Sheet2!A1;0)
The formula showed me a 0 for the first row and 'name 2' for the second row and 0s in all further rows because I had only populated the first two rows of the other sheets.
Anyway, I'd play around with that.
posted by koahiatamadl at 1:28 PM on November 21, 2019
So for example I just pulled up a new Excel file with three sheets - cover, sheet 2 and sheet 3. Sheets 2 and 3 had a names in the first two rows of column A, sheet 2 had a date for both names in column B and sheet 3 had no date for name2.
On the cover sheet I used this formula
=IF(OR(Sheet2!B1=0;Sheet3!B1=0);Sheet2!A1;0)
The formula showed me a 0 for the first row and 'name 2' for the second row and 0s in all further rows because I had only populated the first two rows of the other sheets.
Anyway, I'd play around with that.
posted by koahiatamadl at 1:28 PM on November 21, 2019
If you need the "drag down" approach, you can just type out all the sheetnames down column A, then in column B enter a formula using "INDIRECT" to insert the sheetname value where appropriate. Like this:
=IF(ISBLANK(INDIRECT(A1&"!B5")),INDIRECT(A1&"!B1"),FALSE)
This formula should drag down just fine.
For another approach, a little-known excel trick is the "3D -reference" which allows you to do formulas on the same range across multiple sheets. So a formula like
=COUNTA(Sheet1:Sheet5!B5)
will tell you how many sheets have a non-blank B5, for all sheets between Sheet1 and Sheet5 - this works even if you add sheets between them. And you can name the sheets whatever you want like "Start" or "End".
posted by smokysunday at 2:21 PM on November 21, 2019 [5 favorites]
=IF(ISBLANK(INDIRECT(A1&"!B5")),INDIRECT(A1&"!B1"),FALSE)
This formula should drag down just fine.
For another approach, a little-known excel trick is the "3D -reference" which allows you to do formulas on the same range across multiple sheets. So a formula like
=COUNTA(Sheet1:Sheet5!B5)
will tell you how many sheets have a non-blank B5, for all sheets between Sheet1 and Sheet5 - this works even if you add sheets between them. And you can name the sheets whatever you want like "Start" or "End".
posted by smokysunday at 2:21 PM on November 21, 2019 [5 favorites]
I used concatenate to build the formulas required, then took the results and pasted into notepad to remove concatenation formatting and leave me with a list of formulas to paste.
posted by b33j at 5:46 PM on November 21, 2019
posted by b33j at 5:46 PM on November 21, 2019
This thread is closed to new comments.
posted by eyeball at 12:09 PM on November 21, 2019 [2 favorites]