Using a cell value in a cell reference...

[Xeroid]

I am wanting to use the value of cell A1 as part of the reference in a formula.

For example: =b(a1)+1

I know it can be done, but I can't get the formatting quite right.

Please help.

Regards,

Xeroid.

 

[Tom Urtis]

You might need INDIRECT or maybe something else. Try explaining what the preceding "b" means, and give an example of your data with expected results from the formula.

 

[Xeroid]

Where a1=5,

B5=9,

the formula =b(a1)+1

would hopefully give me a result of 10.

 

[Yogi Anand]

Hi Xeroid:

Try ... =INDIRECT("B"&A1)+1

 

[Aladin Akyurek]

Also with non-volatile INDEX...

=INDEX(B:B,A1)+1

 

[Xeroid]

That got it, thanks!

Xeroid.

 

[tcg2k5]

Qoute "Using a cell value in a cell reference...
I am wanting to use the value of cell A1 as part of the reference in a formula.

For example: =b(a1)+1

I know it can be done, but I can't get the formatting quite right.

Please help.

Regards,

Xeroid."

the formula that you need is =(a1+1)

 

[Viksterr]

Great Stuff!!


I was stuck with the same problem and the solution I got from here worked! Here's how I used it...

=INDIRECT("'Sheet2'!D"&$B2)

I pasted this formula in Sheet1, cell C2.

Thanks again everybody!
Vik :wink:

 

[Aladin Akyurek]

Great Stuff!!


I was stuck with the same problem and the solution I got from here worked! Here's how I used it...

=INDIRECT("'Sheet2'!D"&$B2)

I pasted this formula in Sheet1, cell C2.

Thanks again everybody!
Vik :wink:

Try rather:

=INDEX(Sheet1!D:D,B2)

for INDIRECT is volatile (prolongs the recalc time) and locks you to column D.

 

[immyjimmy]

vlookup concatenated reference

I have a workbook which keeps track of all the reports I run on all our projects. Every two weeks, I run a report on the budget hours vs. the actual hours logged for each project. And I keep the date of the last time I ran each report.
I name the report "BVH 'sales order number' 'customer name' 'date - in yymmdd format'

example:
BVH 4000265 ACME 060614.xls

Now one of these projects is mine and I need to keep closer track of it than all the rest. So I have a workbook for that project with all the contract line items listed, sequence numbers, priorities, release numbers and - budgeted hours per line item. I also have a formula set in one column to do a vlookup in the last BVH report file to compare actual hours spent with the budgeted hours allocated. If the budget is lower than the actual, I have conditional formating set to turn the line item green if budget is higher than actual and pink if actual exceeds budget.

In cell J3, I have the following formula:
=VLOOKUP(A2,'T:\JIM C\EXCEL\[Budget VS Hours to do list.xls]Today'!$B$2:$F$15,5,0)

This shows the date of the last BVH report run. Cell A2 shows the sales order number.

In cell K3, I have the following formula:
=IF(ABS(RIGHT(YEAR($J3),2))<10,"0"&ABS(RIGHT(YEAR($J3),2)),ABS(RIGHT(YEAR($J3),2)))

This gives me the year in a two digit format

In cell L3, I have the following formula:
=IF(ABS(RIGHT(MONTH($J3),2))<10,"0"&ABS(RIGHT(MONTH($J3),2)),ABS(RIGHT(MONTH($J3),2)))

This gives me the month in a two digit format

In cell M3, I have the following formula:
=IF(ABS(RIGHT(DAY($J3),2))<10,"0"&ABS(RIGHT(DAY($J3),2)),ABS(RIGHT(DAY($J3),2)))

This gives me the day in a two digit format

In cell N3, I have the following formula:
=K3&L3&M3

This concatenates yymmdd in the format I use in my BVH report title.

Cell J2 has the following formula:
="'T:\JIM C\EXCEL\[BVH "&A2&K4&" "&$N$3&".xls]Sheet1'!$C$2:$J$1000"

This gives me the path, name and range I want to search for my actual hours logged.

Lastly, I 'WANT' the formula in the rest of column J to read something like this:
=VLOOKUP(E4&" TOTAL",J2,8,0)
or
=VLOOKUP(E4&" TOTAL",INDIRECT(J2),8,0)

But the only way I've gotten it to work is by typing:
=VLOOKUP(E4&" TOTAL",'T:\JIM C\EXCEL\[BVH 4000265 ACME 060531.xls]Sheet1'!$C$2:$J$1000,8,0)

How can I get the vlookup to accept the concatenated reference in J2?
As it is, I need to manually change the reference each time I have an updated report.

Thanks in advace...

Jim

Plan to be spontaneous tomorrow.