Hi everybody,

Looking for some to input formula with drop box.


I have attached a spread sheet and would like if possible for somebody to show which formula would work best.
When different period selected that attached appears in the cell.

Many thanks in advance

Attached Files

revenue report try.xlsx (408.7 KB, 15 views)

Where is this formula on your sheet?

Sorry what I mean is that I would need help with formula to use in drop box

To do what?

When i select a year, a period and a week in the result page i want the correspondent number from data to appear in the appear in the correct cells

Attached Files

revenue report try.xlsx (408.9 KB, 12 views)

Oh...I understand now. This will be pretty difficult, probably beyond my ability, given the way your data table is structured. I'm kind of busy today but I will try to take a look at it tomorrow or Friday. If I can't wrap my brain around it, I will put the word out to some other folks here and see if they can figure it out.

Alternatively, unless there is some underlying reason you have the data table set up that way, may I suggest considering a redesign. It might be possible to design it in a way in which you could get the results much more easily. Filtering or pivot tables come to mind though neither are possible the way it currently is designed.

Thanks Ever so much for your prompt reply no rush.
No problem would be happy to redesign if needed with some help, as data can be design any shape

many thanks

Again

OK...take a look at the attached. I've only entered the data for the two periods in 2015 for which you had data in your table. I have used the Format as Table functionality. If this format is amenable to you, you will see how powerful that functionality and it will further enable other powerful tools too such as the previously mentioned Pivot Tables. But lets start with this and I'll go over a few details about what this can do.

* As you can see, the table is nicely color formatted. This is all automatic. If you insert, add, or delete rows or columns, they will automatically format. If you prefer different formatting scheme, you can select from several at Home: Format as Table.

* I did not include the fields for Occupancy or Average RR. Should you want to add these or any other field you can do it a few ways. First, the cell to the right of the Total header in the first row, type "Occupancy" and hit Enter. Excel will automatically create a new column. You can also insert a column (or row) in the middle of the table and selecting insert from the popup context menu.

* Here's a thing a I like a lot. In the first cell under Occupancy, enter a formula. Anything just to check it out. When you hit enter, Excel copies that formula through all the rows in the columns. Neat huh?

* To add more rows at the end of the table, simply grab and drag the little red triangle in the bottom right of the rightmost cell in the totals row.

* The totals row allows you to summarize the displayed dat in many ways: Count, Sum, Min, Max, Avg, etc. To select any one of these, click on the cell below the data for which you want summary calculation. A drop down button will appear to the right. Click it and select the function you want.

* These calculations will update accordingly to the data displayed through your filter choices. Perhaps you only want to see and summarize the data for period 2. Click the drop down button to the right of the Period header, uncheck 1, and click OK. Only the period 2 data are displayed and the summary calculations update automatically.

That's it for now. If this seems like a way you might want to go, we can delve further into what you would like to do...pivots, charts, etc.

Attached Files

Revenue.xlsx (373.6 KB, 12 views)

Thanks Mate excellent

Much appreciated