Let me explain what I’m trying to do. I want to reference a cell based on a specific column and variable row. So if cell C1 has a value of 1 then I want to show the value of A1, if cell C1 had a value of 5 I want to show the value of A5. Can anyone help??? PLEASE!!

PDF of an example

i emailed you - check your mail.

hi geoff, you can use a really long “if-then” statement. it’s sloppy but you can just type it out in one cell and copy it down the column. It works like this, if i am understanding what you want to do correctly:

=if(c1=1,text(a1,""),if(c1=5,text(a5,""),if(c1=??,text(a??,""))… etc)

The “” lends a character string format(as opposed to a date format, for e.g. for whatever text is in a1 or a5). I tested it a few times with different things. The 2nd and third ifs serve as “else if”, and if the value in C1 does not fit any of the criteria(e.g. c1=8 in your pdf example) then the cell will say “FALSE” then you know that you may have goofed somewhere if you were expecting a person’s name.

Hope this helps. Here are the two examples I followed if you get stuck

=IF(B7>=5000,“high”,IF(B7<=1000,“low”,“med”))

If the number is greater than or equal to 5000, then use the phrase “high,” else if it is equal to or below 1000 use “low.” else the default is “med.”

Convert Gregorian date to Julian date

=TEXT(A6,"yy")&TEXT((A6-DATEVALUE("1/1/"&TEXT(A6,"yy"))+1),"000")

Where the Gregorian date is in cell A6, convert to a date in the format of YYSSS. YY=Year and SSS=day number in the year.

Maybe someone already suggested it…“VLOOKUP” sounds like it might work. From help text:

VLOOKUP

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The V in VLOOKUP stands for "Vertical."

Hope this helps, I can give you more information later if you need it. It’s time for me to go home now…

Cheers, Kate

if those suggestions don’t work for you - try looking around on http://www.experts-exchange.com

there should be a similar question if you search for something about IF statements or vlookup or something - you have to join to see solutions, but the member agreement is reasonable and they have some smart people there -

Lisa

VLOOKUP won't work. It wants the columns to be specified. I'll do some more searching. I guess the only thing I need to find is how I would specify a cell based on a number.

I FIGURED IT OUT!!! Woo hoo!!!

Using the INDIRECT formula like this.

=INDIRECT( “A” & B1)

The A stays constant and the B1 will become B2, B3, etc. as I drag it through the spreadsheet. The B column adds the row value to the letter “A” and inputs the value of that column!

Yes, this just saved me about 10 hours of work!! Thanks to all for your input!!

Geoff

have you looked at MATCH or INDEX functions? I don’t know them very well.

this guy has some interesting info: http://www.xl-logic.com/menu.html

and has a yahoo list here if you want to ask directly:

http://groups.yahoo.com/group/XL-Logic/