Help with Formulas/ if- or v look ups

[Emmaj1985]

Hi all,

Desperate for help before I launch my computer out the window... I am self taught on spreadsheets.. I am getting there but im struggling with one thing.

I don't know what formula-code I need to input into my spreadsheet. When I change the date I want the information associated with the date on display to disappear. and when I put that date back I want that information to come back. Hope you understand what im trying to explain.

Can anyone please explain what I need to do.

Many Thanks

 

[philwojo]

I think you will need an IF formula used in a Conditional Formatting box.

If your formula is TRUE then you can change the color of the text to be white so it essentially disappears. Otherwise it will be the normal font color.

That is the quickest way I can think of to do this and not lose the data.

 

[Joe4]

Welcome to the Board!

I think we need more details.

You mention "information associated with the data".
So is there some sort of table with information that you want to look up?
If so, how is it structured?

If you have your date in the left-most column of your lookup table, and the information you want to return in the columns to the right of the date in the same row, then you can use a VLOOKUP function wrapped in an IFERROR function to do what you want, for example:

=IFERROR(VLOOKUP(A3,LookupRange,2,0),"")

So, if it finds a match, it returns the 2nd column from your lookup range.
If it does not find a match (because you blanked out cell A3, it will return nothing).

 

[bellman101]

Hi Emma,

Some more info I think - when you change the data... what is this? a drop down? just a cell? any one of a range of cells?

The date related data - is this a list where several rows could match? is it a table with a single row or column for each date?

You want them to disappear - you want them to appear as blank/empty cells, or you want the row/column hidden completely?

Can you screenshot any of this?

Cheers
JB

 

[Emmaj1985]

Thanks everyone for your replies so far,

Everytime I try and submit a screen shot the computer crashes?? is there a way of adding an attachment?

 

[Emmaj1985]

 

[Joe4]

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

By the way, did you see my previous post?
Does that solution seem like it would work for you?
If not, why?

 

[Emmaj1985]

http://www.onlinepclearning.com/wp-content/uploads/2013/08/7-08-2013-1-21-27-PM.png

That is sort of what my spreadsheet looks like..... if you click the link above?
so for example on that one in the link on Monday the 5th Melinda Simmonds works at cd.

where it says date commencing if I changed that information a month forward lets say, I want the information associated with Monday the 5th to go and new information on what the person is working on the new date I have put in?

I do want that information to come back though if I put the date back to Monday the 5th/?

sorry for the probably really rubbish description im trying to give... my ehad feels like its about to explode.lol... ive watched many videos and I just cannot master it.

 

[bellman101]

Does this data get saved/archived anywhere?

If so, and that archive is in a data table format, with an ID field, then try this.

=VLOOKUP(SUMIFS(DATA!A:A,DATA!B:B,D10,DATA!C:C,J9),CHOOSE({1,2],Sheet2!A:A,Sheet2!D:D),2,0),0)

Make sure you:

Change DATA to your data sheet name.
Change A:A to the column with the ID (in 2 places),
Change B:B to the column with their name,
Change C:C to the column with the date,
Change D:D to the column with the shift or whatever it is you're looking to return.

Change D10 to the first cell with a name,
Change J9 to the column header that has the date - it may be the one with "mon" or "5". if neither, can you make "mon" a date and format to read "mon", and the same all across or this will be very tough!

You should now be able to fill across.

If you do not have an archive for this, then I'm not sure this is going to be possible.

Cheers
JB