Need to pull dates and qty's only if there's qty > 0 in another c

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to design a forecasting schedule based on customer's demand per
week. The date is in one cell (A1) and 2 rows later is the qty (A4) to fill
if there's demand to fill. I want the dates and qtys to list in the next
worksheet so all the user has to do is copy and paste the list into our
software. There's quite a few of them.

The thing is I don't want the date qty to list if the qty equals 0.
The finished product should look like:
cell1 (A1 DATE) cell2 (qty >0)
cell3 (B1 DATE) cell4 (qty >0)
cell5 (D1 DATE) cell6 (qty >0) [note - C column skipped because the qty
= 0]

Here's the twist, I can't use a macro. My brain hurts already.
 
Hi I think this should do what you need,

On your other worksheet type in cell A1


=IF(Sheet1!A4>0,Sheet1!A1,"")

In cell A2 type

=IF(Sheet1!A4>0,Sheet1!A4,"")


You can then drag these across the other columns to get B1, B2 etc
 
Thank you so much for your help.
I was wondering if a nested If/then statement could eliminate the blank rows
in between. I was looking for a way to not have any blank rows. If the qty
is 0 then it should go to the next date to see if it's greater than 0, if
not, then go to the next row and so on. Do you think this is possible?

Newbeetle said:
Hi I think this should do what you need,

On your other worksheet type in cell A1


=IF(Sheet1!A4>0,Sheet1!A1,"")

In cell A2 type

=IF(Sheet1!A4>0,Sheet1!A4,"")


You can then drag these across the other columns to get B1, B2 etc
--
This post was created using recycled electrons!


rexie3 said:
I'm trying to design a forecasting schedule based on customer's demand per
week. The date is in one cell (A1) and 2 rows later is the qty (A4) to fill
if there's demand to fill. I want the dates and qtys to list in the next
worksheet so all the user has to do is copy and paste the list into our
software. There's quite a few of them.

The thing is I don't want the date qty to list if the qty equals 0.
The finished product should look like:
cell1 (A1 DATE) cell2 (qty >0)
cell3 (B1 DATE) cell4 (qty >0)
cell5 (D1 DATE) cell6 (qty >0) [note - C column skipped because the qty
= 0]

Here's the twist, I can't use a macro. My brain hurts already.
Click to expand...
Click to expand...
 
Hi,

You say hide rows with zero, but I'm guessing you meant hide columns with no
data on sheet 2, as these columns are blank if the value was 0.

Hope thats what your after,

I know you wasn't looking for a macro, but I think it will be the easiest
way, well maybe!

I have modified a previous posting that someone had made to suit, following
the ranges you listed before.

On sheet 2, right mouse click and select "View Code"

Click "insert" on the menu, then "module"

On the right hand side of the screen copy and paste the following code;
-----------------------------------------------



Sub Hide_EmptyColumns()
'To hide columns with no data in rows 1:2

Application.ScreenUpdating = False
With Sheets("Sheet2")
Dim col As Range
For Each col In .Range("A1:IV2").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0

Next
End With
Application.ScreenUpdating = True
End Sub

Sub UnHide_EmptyColumns()
'To unhide columns with no data in rows 1:2

Application.ScreenUpdating = False
With Sheets("Sheet2")
Dim col As Range
For Each col In .Range("A1:IV2").Columns
col.EntireColumn.Hidden = False

Next
End With
Application.ScreenUpdating = True
End Sub


--------------------------------------------------
Save then close this screen to go back to your Excel sheet.

To create shortcuts to run the macros easier


Select "tools" from menu, then "Macro" then "Macros"

Your should see another screen that has two macros one called

Hide_EmptyColumns()

and one called

UnHide_EmptyColumns()


Highlight one by clicking;

Then press "Options" where you see shortcut key enter a letter, ie h for
hide, but it can be anything, then ok

Do the same for the other macro and maybe use u for unhide.


Ok on your sheet two when you press ctrl+h the columns that hold no data
will be hidden.

To unhide use ctrl+u

You can then save, print etc, and the Ctrl+ H or U allows you to update very
easy when more data is added to sheet 1


Note, I have set this to apply from column A right through to column IV, it
will be a lot quicker if you change IV to your last actual column to be used
thats on sheet1

Hope thats what you was after.
 
I so very much wish I could use a macro but I can't. Most of the users are
on thin clients and IS is backed up. We can't load anything new in the
server.

But to answer your question, the date and corresponding qty are going to in
a row then the next date and qty if the qty > 0. But if the qty = 0, then I
don't want the date and qty (0) row showing up on sheet 2. I would want it
to look for the next date that has a qty > 0.

I have 14 weeks to forecast per line item.
 
Hi shame you cant use a macro,

I reckon what you want could be done, although I not sure how without a
macro, I think it would be worth doing a new post, saying what your after and
that you cant use macro's!

Best of luck,
 
I know what you mean. I know there's a way to do this but I just haven't
figured it out yet. Thank you anyway.
 
Back
Top