Populating a cell if the cell next to it is empty

[bearcub]

I have a spreadsheet which lists members who hold multiple leadership positions.

I have an array that pulls positions from a different sheet. If they hold 3 different positions, then I would like the position to be on 3 rows, instead of one.

Right now, for example, Joe Blow holds there positions - golfer, fighter, ball player. He comes out of our data base with 3 records, one for golfer, one for fighter and another for ball player.

My spreadsheet it linked to the database extract, so I have 3 rows on my spreadsheet, one for each role. However, I have to do a workaround on another sheet because the name that comes out is different than what we report. I have to use a mapping table so that I have Pro Golfer, Bad Fighter, Average Ball Player.

So, my formula will look at the Member ID ( I'm using match, small and Aggregate to pull the mapped data from different tables on another worksheet) and retrieve Pro Golfer, Bad Fighter and Average Golfer for this member. Instead of populating 3 rows all the positions are in one row.

I tried using If the cell is empty then "" or otherwise run the formula. But, since this sheet is linked to the database sheet, it isn't "" - because I have a formula there. Though the cell looks empty there is a formula pulling the data over from the data extract.

The data comes out in 1 column but I'm my formula is converting this into separate columns. I am getting the correct results except that they appear in multiple columns on one row as opposed to multiple columns across multiple rows.

I hope this makes sense.

Instead of getting

Pro Golfer
               Bad Fighter
                                 Amateur Ball Player

I'm getting

Pro Golfer    Bad Fighter    Amateur Ball Player

Do you think this possible given that none of my cells are empty of a formula?


thank you for your help,

Michael

 

[tdh777]

Hi Michael,
If you post the actual formulas or VBA code in question and explain which formula belongs to which cell then it will make it much easier for people to help you.
We can't "fix" your code if we can't see it. (and we don't want to spend ages re-writing your code based on your description if all it needs is a little adjustment!)
Cheers,
Warren K.

 

[bearcub]

Okay, I was thinking about doing that but the formula is huge so I didn't want to cloud the issue even further.

Here is one of the formulas that I have in a column. I have the same formula point at different tables for different positions.

INDEX('LocalStateSCC Falcon Positions'!$C$3:$C$800,AGGREGATE(15,3,('All SCC Ready to Import'!$A38='LocalStateSCC Falcon Positions'!$A$3:$A$800)*(ROW('LocalStateSCC Falcon Positions'!$A$3:$A$800)-ROW('LocalStateSCC Falcon Positions'!$A$2))/('All SCC Ready to Import'!$A38='LocalStateSCC Falcon Positions'!$A$3:$A$800),COUNTIF($A$35:A38,'All SCC Ready to Import'!$A38)))

Thank you for your help.

The destination worksheet is the All SCC Ready to Import sheet and the source sheet is the LocalStateSCC Falcon Positions sheet.

Michael

 

[tdh777]

Hi Michael,

Well at least we have a bit more of the picture now, although obviously without seeing any actual source data (or sample data) or locations of the formulas and tables it still is partly like trying to drive a car blindfolded with someone sitting in the passenger seat giving directions...

Anyway, it appears that the formula you shared is an Array formula.. otherwise I don't see the point in using the AGGREGATE function and the other complex structures in the Formula (which are "classical" Arrayformula techniques). [So therefore I assume that the range of values it returns are spread across a number of cells, as illustrated in your original post.]

If this is the formula that returns the "Pro Golfer Bad Fighter Amateur Ball Player" that you refer to in your original post, then basically to get the result you want of spreading the entries over several lines would require either :

1. Updating the ArrayFormula to return N sets of N values, rather than 1 set of N values (which would make the formula significantly more complex and thus less maintainable), OR
2. Simply having the current formula / values in a hidden "helper" row and then using normal simple formulas in the subsequent rows to copy/reposition the values across multiple rows/columns, OR
3. Place the current formula onto N different rows and adjust each formula by wrapping it in logic to only show the Nth value from the set (eg use ROW() or COLUMN() to generate an effective array of 1,0,0 , 0,1,0, 0,0,1 etc - or could just be an IF statement).

In all cases thought his begs the question Why do you want to spread the values across N rows AND N columns ? This is a pretty unusual outcome, so perhaps if you let us know / why you are ultimately trying to achieve we may be able to offer some alternative (simpler) approaches.

Cheers,
Warren K.

 

[bearcub]

thank you Warren for the explanation though I'm a little lost in your explanation -- though I get the gist of it (I need to have it translated - some of the terms are going over my head).

I'm working with different systems to create a workaround so they all fit together. The data that I'm referring to is thrown into an Access database to run a one time leadership directory report. The data columns out of the company database in row format (all in one column). For the data to be shown properly in the access reports it news to be separated out by Local, State or Service Council positions because we run different reports based upon what they do. The state positions are not maintained in the current database but comes in a spreadsheet from another department.

Can you provide me with an example of what you're referring to? If you it is too complex, as you suggest, then I think that it wouldn't be practical. I'm creating this tool for admins to use so if it is too complex they may not know how to fix it if something breaks. That is my concern as well, to keep it as simple as possible.

Thank you for your help,

Michael

 

[tdh777]

Hi Michael,

I wrote you a nice long explanatory answer but this site and/or Chrome isn't smart enough today to cope with me using the actual URL "Link" function in the text and lost it all... (twice !) [Instead you have to enter the URL in the TEXT and let it turn it into a hyperlink automatically !!]

Anyway, here's the short version : Check out this thread on Mr Excel : www.mrexcel.com/forum/excel-questions/1047040-creating-list-table.html ("Creating a List from a Table") which has a solution to the same requirement that (I think!) you have to reformat your results grid into a series of Rows suitable for import in Access.

The other advantage of having a separate section (or sheet) containing the final data in "Access-friendly" format is that you don't have to change your existing formulas and they will be more maintainable into the future (being in 2 separate sets of formulas rather than one "mega-formula" ).

Cheers,
Warren K.

 

[bearcub]

Thank you Warren for the link. Peter has helped me before so I know he comes up with great answers.

I'm looking at the question and it looks like promising but I don't think it will work. It is actually the reverse of what I need. All the positions are in one column (close to 100 different designations) and I need to have it spread across 3 different columns. I think what I need is something that will look to see if there is something in column B, if there is, the value should go into the next row column C. Then, in column D, if there is anything in column A or Column B then put it in the next row.

I don't think the formula works like this but I think it is good one to keep in my archives though. Definitely really cool.

Sorry you spent time writing an email but couldn't send it. Thank you for all your time and help,

Michael