Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've built a system for managing our hotel's reservations. I'm a self-taught excel user and know only what I've figured out over the past months digging through the internet.

I've hit a problem. Each day of the year has it's own 24-room list with info such as room number, guest name, passport number, paid y/n, nationality etc. I want to give each of these 365 areas its own number relating to the day of the year e.g. january 1st = 1

I was hoping that I could then use this master spreadsheet to automatically fill out a daily guest list by using an if formula =if(cellref = 1 .... and it would fill out my daily form with all of the info I required such as names, passport numbers, paid y/n etc.

In my mind I'm forseeing a huge mess of a formula that has to account for the 365 possibilities of the original spreadsheeet/worksheet.

How can I fill out the daily form with the info from the yearly spreadsheet by using a formula? Is there a simple way or will I be typing essay-length formulas into eah cel of my daily form?

ANy help appreciated, I'm going to crack it, just need some guidance.

share|improve this question
    
Your question is a little muddled, what is a 24-room list does your hotel have 24 rooms? What are the 365 areas? Are they each on 1 sheet or on the same sheet? and why isnt the hotel using a database!! –  Karthik T Jan 26 '13 at 17:31
    
using an if formula =if(cellref = 1 .... and it would fill out my daily form with all of the info - Where does this info come from? –  mcalex Jan 26 '13 at 17:49
    
Have a look at =indirect - at a guess, it might be possible to get what th'art asking for by using it to refer to a cell whose reference is determined using an arbitrary formula. –  Darael Jan 26 '13 at 18:24
    
Adding my voice to the other comments, what you want could be quite simple to accomplish, but it depends on how you've laid out your original spreadsheet. With a little more information from you, we can figure out how to help. (And welcome to SuperUser!) –  chuff Jan 27 '13 at 1:34
add comment

1 Answer

I'm going to make a few assumptions here, but I think I know what you're getting at. Presumably you have one sheet that has a formatted "daily guest list" and another sheet that you're pulling the data from (since 365 worksheets is probably a bit unmanageable). Without seeing your format, I'll assume that in your master spreadsheet Column A has the day of the year (1 - 365) and Column B has the room number (1 - 24). I'll further assume that you have this sorted so by Column A so that all of the rooms for the same day are contiguous (this is important for my example, but you can get around it with more complicated logic).

On your daily guest list worksheet, put the day number you want to run it for in a cell (A1 in my example).

B1:

=MATCH(A1,Sheet1!A:A)

If row 2 contains labels, then we'll start row three with your pulled data:

=INDIRECT(ADDRESS(ROW()-3+$B$1,COLUMN()+1,4,1,"Sheet1"))

You can copy that formula across columns and down 24 rows to grab the data from your master list. =indirect() grabs a cell based on a text string. address() creates a string referring to a cell based on the row number, column number, a couple formatting options, and the sheet name.

I specify the row number by getting the formula cell's row with =row(), subtracting three since I'm assuming the first two rows are labels and such, and then adding the row number of the first row that has the correct day (entered in A1 and found via the =match() formula).

I offset the column by 1, since I figure you don't need the date identifier repeated on this daily guest list.

The two formatting variables don't change how this function works when paired with the =indirect() function. The final variable is the sheet name entered as a string. You could put the sheet name in a cell and reference it if you have to pull from various sheets. In the above examples, you'll have to replace "Sheet1" with whatever sheet name you've chosen for the data.

I hope that this is instructional enough for you to run with it from here. There are many ways to make this work (you don't need B1, for example, but I thought it made the example clearer).

Finally, I would recommend using dates instead of just an integer. It'll keep things really clear, and you can even do convenient things like use =today() for today's date.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.