Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
Our spreadsheet pulls values from a column when a month is inserted in
a certain cell: OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets. OCT (B2) OCT NOV DEC Complaint 3 (B10) 3 6 Inspection 10 (B11) 10 2 NOV 11 (etc) 11 5 Door Notice 1 and so on Recheck 32 Citation 2 Work Order 0 Demolition 0 Don't know if when this is posted it will line up OK. Any help appreciated. Thanks, James |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
Entr in B9
=INDEX(K9:V9,MATCH($B$2,$K$8:$V$8,0)) and copy/drag down as far as necessary (through row 286) If you could spare a cell, say B3, to contain the index value, you could put in it =MATCH($B$2,$K$8:$V$8,0) and change the first formula to =INDEX(K9:V9,$B$3) The latter approach would be more efficient. HTH Declan O'R Dasin wrote: Our spreadsheet pulls values from a column when a month is inserted in a certain cell: OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets. OCT (B2) OCT NOV DEC Complaint 3 (B10) 3 6 Inspection 10 (B11) 10 2 NOV 11 (etc) 11 5 Door Notice 1 and so on Recheck 32 Citation 2 Work Order 0 Demolition 0 Don't know if when this is posted it will line up OK. Any help appreciated. Thanks, James |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
How about a MATCH with an OFFSET:
The match part will convert the month to a number. Ex: =MATCH("Oct",{"Jan","Feb","Mar","Apr","May","Jun", "Jul","Aug","Sep","Oct","Nov","Dec"},0) returns 1 through 12, indicating which month was selected. Then the OFFSET can move you over that many columns from a reference cell. Ex: =offset(A10,0,match($B$2,{"Jan","Feb","Mar","Apr", "May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0 )) This figures out what month you're looking at, and moves right that many columns from the reference cell, a10 in this case. --Bruce "Dasin" wrote: Our spreadsheet pulls values from a column when a month is inserted in a certain cell: OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets. OCT (B2) OCT NOV DEC Complaint 3 (B10) 3 6 Inspection 10 (B11) 10 2 NOV 11 (etc) 11 5 Door Notice 1 and so on Recheck 32 Citation 2 Work Order 0 Demolition 0 Don't know if when this is posted it will line up OK. Any help appreciated. Thanks, James |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
OOOPS!
I should have added that I assumed your month headers (OCT,NOV, etc.) were in K8:V8. If they are in a different row, substitute that row number for the number 8. Declan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
Thanks for the response! I really appreciate it.
I haven't used MATCH or INDEX before, but I think I get the drift. I tried both of your suggestions but I am getting a #N/A in the cells/column where I tried it. I tried using B4 for the index and got the same error. Just figured it out. It is case sensitive! If I type oct I get the error, if I type Oct it works! Is there a way around this? You know users, some won't type the correct Oct, Nov etc. and will get the #N/A error and I'll be getting calls. But what an elegant solution to the IF problem!!! Thanks, James |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
James,
MATCH, as used, should not be case-sensitive. I believe the problem may be due to a space being entered with the value "oct", either before or after, and therefore not visible. There are two solutions: 1. Substitue TRIM($B$2) for $B$2 in the MATCH function - this removes surrounding spaces or, preferably, 2. Use Data Validation to force entry of the correct value in B2. - Select B2, and choose Data/Validation from the menu. Under Allow: choose List. Then click in the Source: field that appears and then highlight the list of months in your spreadsheet. This should cause =$K$8:$V$8 to appear in the Source field. Click OK. Now, when you click in B2, a drop-down arrow will appear and willenable selection of a valid month. other entries can be keyed in but will be rejected. HTH Declan O'R |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
I didn't see any blanks, but I tried the drop down box and it works
great! Now my issue is that the font in the drop down box is extremely small for some reason, but when it populates B2 it takes on the font size of B2. I tried changing the font size on K9:V9 where it is pulling the list from, but it didn't help. I tried it on a blank sheet and it worked fine. I'll keep trying to correct that small issue, but I want to thank you for your help! You have been great and I have learned from it! Thanks! James |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
What font size is set in B2 and what is your zoom ratio for the sheet?
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
James
The small font is not an issue that has bothered me before, but some research indicates that it cannot be changed. However, it appears to me that the actual size is relative to the sheet zoom factor. So if you have a less than 100% zoom factor, your apparent drop-down font size will be proportionately smaller. Your other (cell) font sizes may appear to be OK because you may have compensated for the low zoom factor by increasing the cell font sizes. This would explain why it seems to be ok on another sheet, which probably has a zoom factor of 100%. If this is the case, increase your zoom factor and reduce your cell font sizes. Then it should be ok. When all else fails, you might want to try the approach described here http://makeashorterlink.com/?X27B12274 As for the thanks - you're very welcome. We all learn from these forums. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
Albertus Extra Bold 16 for B2 and 50% (I don't know who picked that
font!). Zooming in helps, but then it requires a lot of scrolling. I tried it on a blank new sheet and it works fine, but this sheet is very large and it was set up with smaller fonts to try and make it fit our monitors a little better. BTW I tried the TRIM idea and still am getting the #N/A error. I looked at athe cell carefully and there are no spaces in the front or back. The only other thing is that it is formatted for center. Thanks again, James |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
I'll give it a shot!
Thanks, James |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF problem - help please
James
Formatting should not cause the #N/A error. There must be something other than "oct" either in B2 or in the K8. The fact that it works with the data validation only proves that if you put whatever is in K8 in B2 the formula will find it, but if the contents of K8 are "Oct " ("Oct" with space following), putting "oct" in B2 without a following space will not work. As for the small drop-down font, you have just what I thought you had - a small zoom and large cell font, which will make your drop-down font very small. I assume you have corrected it by now by reducing the font size in all your cells, changing the row heights and column widths to match the new font size and increasing the zoom factor. I see little reason for zoom factors of less than 100% unless you want to see your overall sheet layout, after which you would revert to 100%. It also helps to show more on smaller screens of course, but you then negate the effect by using such large font. Regards Declan O'R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with nested IF_OR statement | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
€śUse of more then 7 nested if statement€ť | Excel Worksheet Functions |