Formula to populate columns with text

[Ms2BSwagg]

Hi,

Is there a formula or a way that if i input a time allocation for so many months, it then populates the months in coloums?

Thanks in advance

M2BS

[Special-K]

Yes.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Ms2BSwagg]

ThankS, I'm not sure if a VBA would be better as the months will not always be the same.

attached file.

[Special-K]

Apart from your descriptions in red (which must ultimately be changed to numbers) there's nothing in the spreadsheet to indicate that a time should be entered in a particular month.
Something like a check box for each month so the total time can be divided into the chosen months.

[Special-K]

Maybe something like this?

[Ms2BSwagg]

Ah sorry, the specified time allocation in cell c3 should populate in the cells that had the red. So in your file where the 12 and 6.66666667 is it should show the allocation from c3.

Thanks for the file tho.

[Sam Capricci]

well from what Special-K gave you and your description it sounds like what he gave you should still work. Just change the formula to this...
=IF(D4="x",$C3,"")
you still will need a helper row unless you want to use visual basic.

[Special-K]

Just change the formulas to

=IF(D4="X",$C3,"")

[Ms2BSwagg]

@Sambo kid - I don't mind the vba, would you be able to help with that?

@Special-K thanks will try that.

[Sam Capricci]

No, sorry, I don't have VBA skills. I think Special-K gave you your best option for using a formula.
You could ask a moderator to move this post to the VBA section to get help there.

EDIT: and honestly, I still think the VBA would need some way to recognize where to put the values so something like the helper row might still need to be employed.

[Ms2BSwagg]

Thank you so much both

[Ms2BSwagg]

Sorry, with the above, from the data used in the file, could you then take the results and make them appear on a new sheet?

So you use like a data sheet with the helper file and then on the next sheet would be the output?

[Special-K]

Delete rows 4 and 6 on Sheet1 (the rows with Xs)

Copy the sheet into a new sheet (Sheet2) so it's indentical to Sheet1

On Sheet1 place Xs where you want the times to appear

Change the formulas in Sheet2 to

=IF(Sheet1!D3="X",Sheet1!$C3,"")

Sheet 1 now has an X where you want the time to appear
Sheet 2 is a copy of Sheet 1 but where the Xs are on Sheet 1 you should now have the times.

[Ms2BSwagg]

Don't worry blonde moment. Thank you that works great

[Sam Capricci]

this would be one way ...
insert new tab, copy over your project info and times and dates but leave out the helper row.
then use this vlookup =IFERROR(VLOOKUP($B3,Sheet1!$B$2:$J$6,COLUMN(C$1),FALSE),"") dragged across and down.

[Ms2BSwagg]

Yes that works too thank you

[Sam Capricci]

Glad that will work for you and you're welcome!
And thank you for the reps!

[Ms2BSwagg]

Thank you

[Ms2BSwagg]

Urrrrrrrggg sorry me again, can the be added to the formula so that if the the cell is empty it shows 0 ?

[Sam Capricci]

change the formula in D3 of sheet2 to this...
=IF(VLOOKUP($B3,Sheet1!$B$2:D6,COLUMN(C$1),FALSE)="",0,VLOOKUP($B3,Sheet1!$B$2:D6,COLUMN(C$1),FALSE))
then drag down and across and you'll have zeros in the empty cells.
I changed this part too Sheet1!$B$2:D6 so that as you drag it toward the right and down it will keep indexing to cover more columns and rows.

[Ms2BSwagg]

Hey,

Thank you, for the above. Will try it out.