Leave a cell blank until data is entered in different cell

[sulcrow]

I have searched the forums for an answer to my problem and I have found related posts but cant work out how to apply them to my issue.

Cell F48 = Base value will be 10
Cell G48 = Can be anything up to 10 and will be subtracted from cell F48

I want cell F48 to remain blank until a value has been entered in G48.

For example I enter 3.75 into G48 and F48 will then display 6.25.

Can anyone answer this please? Many thanks!

 

[Eric W]

Maybe:

=IF(ISBLANK(G48),"",10-G48)

 

[sulcrow]

Maybe:

=IF(ISBLANK(G48),"",10-G48)

Thankyou! This works perfect, if I wanted to change it so that F48 could be different to 10 in some cells, what would I need to change?

 

[Eric W]

I'm not entirely sure what your question is. Please post some examples with the actual data and the expected results. If you're just asking how to change the constant of 10, then you can change the 10 in the formula to something else, like:

=IF(ISBLANK(G48),"",8-G48)

 

[sulcrow]

There isn't an easy way to describe it really. Basically cell f48 is the input for hours worked as a picker and f48 is for other tasks within the warehouse away from the picking job. 10 is for 10 hour days which can sometimes change to 8 or 12 hour days. Can there be a way so that just inputting 8 into cell c48 it will minus off f48 without having to manually change the formula each time the amount of hours available for that day changes? Sorry if this doesn't make any sense I dont know how else to explain it easily

 

[sulcrow]

Basically if I have the formula you suggested, is there a way it can be modified so that I can override the formula if the number of hours changes from 10 to something else?

 

[Eric W]

Sure. If you put the number of hours in a day in cell Z50 (or whatever works for your spreadsheet), change the formula as follows:

=IF(ISBLANK(G48),"",Z50-G48)

Then you can just change Z50 as needed.

 

[sulcrow]

Sure. If you put the number of hours in a day in cell Z50 (or whatever works for your spreadsheet), change the formula as follows:

=IF(ISBLANK(G48),"",Z50-G48)

Then you can just change Z50 as needed.

The way my spreadsheet is set up that wouldnt work

Monday to Thursday are 10 hour days so the original formula works fine for those days. Friday, Saturday and Sundays can vary in length of shifts because they are overtime hours. So if a Friday in say Z50 was 8 hours but another Friday in Z57 was 5 hours I wouldnt be able to use it the way you've suggested. I need an over ride function or something lol

 

[Eric W]

You're just giving me a piece of the puzzle at a time! It's hard to come up with a complete answer when I don't know all the requirements. Is this spreadsheet going to be used daily, or once a week? Do you have a list of multiple people with hours to keep track of? Do you have a full week's data when you run this, or is it daily? Do you put in the Friday-Sunday shift hours on Friday-Sunday, or daily? You could have a list of seven numbers somewhere, say Z51:Z57, with 10, 10, 10, 10, 0, 0, 0 for the hours from Monday to Sunday. The formula could get the current day of the week and pull out the hours from the list. You'd override the Fri-Sun hours if need be.

But I hesitate to create a formula unless I know more about what you need. Could you post a section of your workbook, with sample data, and expected results? And say what would change if there are overtime hours or not?

 

[sulcrow]

You're just giving me a piece of the puzzle at a time! It's hard to come up with a complete answer when I don't know all the requirements. Is this spreadsheet going to be used daily, or once a week? Do you have a list of multiple people with hours to keep track of? Do you have a full week's data when you run this, or is it daily? Do you put in the Friday-Sunday shift hours on Friday-Sunday, or daily? You could have a list of seven numbers somewhere, say Z51:Z57, with 10, 10, 10, 10, 0, 0, 0 for the hours from Monday to Sunday. The formula could get the current day of the week and pull out the hours from the list. You'd override the Fri-Sun hours if need be.

But I hesitate to create a formula unless I know more about what you need. Could you post a section of your workbook, with sample data, and expected results? And say what would change if there are overtime hours or not?

Sorry Eric! The spreadsheet is updated Monday to Friday (any weekend work is updated on a Monday) to track multiple pickers scores.

You can see in this ss where your formula is currently working. I have just thought about this in depth after reading your post and realised that there isnt a formula to calculate the Friday, Saturday and Sunday hours because they change so often and cant be defined. Sorry for wasting a little bit of your time but you can now see what you've helped me to fix! Have a good day