Lookup row, dependant on cell value?

I have a sheet,  as per the example. I need to insert into column A the 
quantities, depending on the value a specfic cell.

I.e if week 2 is entered, the column A should read, 12,5,2,3,3,3 and if Week 
1 is entered 5,3,2,3,2,2 etc

I have looked at using HLOOKUP, however this does not replicate the 
reference cell corrected unless each line is configured serperately.

What is the easiest way to acheive this. At the moment I need to select the 
column manually, and replicate down the sheet, rather than just enter the 
required week number into a cell, and let excel adjust automatically.

      1 2 3 4 5
      x Location 5 12 1 3 2
      x Alabama 3 5 6 2 2
      x Denver 2 2 3 4 2
      x Boston 3 3 5 9 2
      x New York 2 3 9 5 2
      x Seattle 2 3 4 6 9



      Week Number 2


0
si1 (22)
1/11/2007 11:10:55 AM
excel 39879 articles. 2 followers. Follow

3 Replies
623 Views

Similar Articles

[PageSpeed] 43

With your data set occupying B2:G7 and with the week number entered
into cell C9, enter this formula in A2:

=INDEX(C2:G2,C$9)

and copy down to A7.

Hope this is what you wanted.

Pete

Simon wrote:

> I have a sheet,  as per the example. I need to insert into column A the
> quantities, depending on the value a specfic cell.
>
> I.e if week 2 is entered, the column A should read, 12,5,2,3,3,3 and if Week
> 1 is entered 5,3,2,3,2,2 etc
>
> I have looked at using HLOOKUP, however this does not replicate the
> reference cell corrected unless each line is configured serperately.
>
> What is the easiest way to acheive this. At the moment I need to select the
> column manually, and replicate down the sheet, rather than just enter the
> required week number into a cell, and let excel adjust automatically.
>
>       1 2 3 4 5
>       x Location 5 12 1 3 2
>       x Alabama 3 5 6 2 2
>       x Denver 2 2 3 4 2
>       x Boston 3 3 5 9 2
>       x New York 2 3 9 5 2
>       x Seattle 2 3 4 6 9
> 
> 
> 
>       Week Number 2

0
pashurst (2576)
1/11/2007 12:55:55 PM
Perfect!!

thanks

"Pete_UK" <pashurst@auditel.net> wrote in message 
news:1168520155.284597.168940@i56g2000hsf.googlegroups.com...
> With your data set occupying B2:G7 and with the week number entered
> into cell C9, enter this formula in A2:
>
> =INDEX(C2:G2,C$9)
>
> and copy down to A7.
>
> Hope this is what you wanted.
>
> Pete
>
> Simon wrote:
>
>> I have a sheet,  as per the example. I need to insert into column A the
>> quantities, depending on the value a specfic cell.
>>
>> I.e if week 2 is entered, the column A should read, 12,5,2,3,3,3 and if 
>> Week
>> 1 is entered 5,3,2,3,2,2 etc
>>
>> I have looked at using HLOOKUP, however this does not replicate the
>> reference cell corrected unless each line is configured serperately.
>>
>> What is the easiest way to acheive this. At the moment I need to select 
>> the
>> column manually, and replicate down the sheet, rather than just enter the
>> required week number into a cell, and let excel adjust automatically.
>>
>>       1 2 3 4 5
>>       x Location 5 12 1 3 2
>>       x Alabama 3 5 6 2 2
>>       x Denver 2 2 3 4 2
>>       x Boston 3 3 5 9 2
>>       x New York 2 3 9 5 2
>>       x Seattle 2 3 4 6 9
>>
>>
>>
>>       Week Number 2
> 


0
si1 (22)
1/11/2007 2:19:24 PM
Thanks for the feedback, Simon.

Pete

Simon wrote:

> Perfect!!
>
> thanks
>
> "Pete_UK" <pashurst@auditel.net> wrote in message
> news:1168520155.284597.168940@i56g2000hsf.googlegroups.com...
> > With your data set occupying B2:G7 and with the week number entered
> > into cell C9, enter this formula in A2:
> >
> > =INDEX(C2:G2,C$9)
> >
> > and copy down to A7.
> >
> > Hope this is what you wanted.
> >
> > Pete
> >
> > Simon wrote:
> >
> >> I have a sheet,  as per the example. I need to insert into column A the
> >> quantities, depending on the value a specfic cell.
> >>
> >> I.e if week 2 is entered, the column A should read, 12,5,2,3,3,3 and if
> >> Week
> >> 1 is entered 5,3,2,3,2,2 etc
> >>
> >> I have looked at using HLOOKUP, however this does not replicate the
> >> reference cell corrected unless each line is configured serperately.
> >>
> >> What is the easiest way to acheive this. At the moment I need to select
> >> the
> >> column manually, and replicate down the sheet, rather than just enter the
> >> required week number into a cell, and let excel adjust automatically.
> >>
> >>       1 2 3 4 5
> >>       x Location 5 12 1 3 2
> >>       x Alabama 3 5 6 2 2
> >>       x Denver 2 2 3 4 2
> >>       x Boston 3 3 5 9 2
> >>       x New York 2 3 9 5 2
> >>       x Seattle 2 3 4 6 9
> >>
> >>
> >>
> >>       Week Number 2
> >

0
pashurst (2576)
1/11/2007 2:37:29 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

How to place a stable title row?
Hi there! For a scientific analyse I need to create an Excel table with more than 1.000 rows content. Now, how can I create a title row that doesn't scroll with the rest of the content, so one that is always visible at the top, while you scroll up and down the rest of the content. I hope that's possible like this! Otherwise, it will be pretty difficult to enter the data into the correct (of about 15) column. Thanks for your help in advance! One way: Assuming headers are in row1, Select A2. Click Window > Freeze pane This freezes the 1st row, so it always remains in view w...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

Combo Box initial values question
Does anyone know how to make a combo box show a value when a sheet opens? Mine are always blank when I open them until I select a value. thanks tp Hi Teepee, Try something like: Me.ComboBox1.ListIndex = 0 --- Regards, Norman "teepee" <teepee@noemail.com> wrote in message news:4645ed29$1@newsgate.x-privat.org... > Does anyone know how to make a combo box show a value when a sheet opens? > Mine are always blank when I open them until I select a value. > > thanks > > tp > > thanks for trying. says 'invalid use of me keyword.&...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...