Excel Help!

[ldcrooks]

Hi there,

I was wondering if anyone could help me - we are moving all from Google sheets over onto Excel and my head is gone. I have used Google Sheets for that long I can't get something simple to work in Excel for me.

See the attached workbook (please ignore the mess of it - it has a long way to go!) - I would like to write a formula in the "HOLDSOLD TRACKER" tab - to pull anything from all of the other tabs where the columns "24 hour hold by" & "Sold by" are not empty.

Please help! I have about 20 other formula tabs to try and figure out after this.

Thanks so much in advance.

[#2]

I, for one, can't ignore what you're calling "the mess of it."

It seems to me, if I can say this gently, that what may be more important that getting a formula or two to work is getting a design that isn't such a mess. It's not clear to me that you need all the separate sheets, for starters. I didn't do an exhaustive analysis of the column headings, but since you want to bring data from all those sheets into the one "HOLDSOLD TRACKER" tab, I'm making the assumption that they must have a lot of overlap, and therefore could be consolidated into a single database, which would then be a lot more useful.

That may not be the case, I realize....but even with that disclaimer, were we sitting down face-to-face, I'd be starting with a focus on overall design first, and by design I don't mean aesthetics. That is to say, I'd leave the fancy colors and fonts till the very end, and use them sparingly. Focus on functionality first.

What is the basic data being collected on the "Input" end? Can it be meaningfully and usefully organized into one table, using columns to differentiate Kia from Renault or other brands, rather than creating different sheets, etc....
What is the end goal or "Output" desired.

The formulas can come later......they're simple (relatively speaking) once a good fundamental design is in place.

[#3]

Thanks for your reply. I definitely understand the frustration of looking at this - it has been downloaded from Google Sheets and dumped on my lap to 'sort out'.

I do like the thought of it all being on the one database also - currently trying to put it together to see if it will work that way. I really appreciate your response - I will be back soon with a more condensed starting point.

[#4]

@mathetes 

Back to basics now. Have some sample data in the Stocklist Tab.

For the 'HOLD/SOLD TRACKER' would it be possible just to bring across just the rows that have info in the "24 hour hold by" or "Sold by" columns from the Stock Tab, sorted by the "Expected Delivery Date" Column?

Thanks so much for your help.

[#5]

@ldcrooks 

Here's the formula that gets those rows. You only enter it once, in cell A3...the results "Spill" into adjacent rows and columns. You DO need the most current version of Excel for this to work. SORT still needs to be added, but I haven't time.

=FILTER(STOCKLIST!A3:AZ21,(NOT(ISBLANK(STOCKLIST!Y3:Y21)))+(NOT(ISBLANK(STOCKLIST!Z3:Z21))))

And here's a video that explains how FILTER works. And the video explains SORT too, so incorporating it will be a good learning experience for you.

https://www.youtube.com/watch?v=9I9DtFOVPIg

P.S. I see you couldn't resist adding the aesthetic bells and whistles--fancy colors, vertical column headings, etc. That really is a mistake, because it can often get in the way of functionality. Save that, if it's even necessary, for the end. And only for the output sheets. You do not need it on the database itself.

[#6]

Thank you so much, I really do appreciate this.

[#7]

You are welcome!