Returning a value from another sheet

lovleigh

New Member
Joined
Apr 29, 2012
Messages
7
I would like a formula that will look in cell R1 of sheet "board uninsured" and based on the number in that cell, look at a specific cell on another sheet and return to value in G12 of "board uninsured". R1 will contain a number corresponding to the month. For instance, 1 is January, 2 is February etc. If the number is 1, I need the formula to look at the "combined" sheet cell E115 for the data to populate in cell G12 of "board uninsured". If cell R1 contains a 2 I need it to look at cell F115 on "combined" and populate that data into G12 of "board uninsured". And so on.

Thank you in advance
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

venomxx6

New Member
Joined
Jan 4, 2014
Messages
3
Here is one solution. I created a sheet based on your description. I think this will do what you want. (it's not fancy) This formula would go in "board uninsured" cell G12 =IF(R1=1,combined!E115,IF(R1=2,combined!E116,IF(R1=3,combined!E117,IF(R1=4,combined!E118,IF(R1=5,combined!E119,IF(R1=6,combined!E120,IF(R1=7,combined!E121,IF(R1=8,combined!E122,IF(R1=9,combined!E123,IF(R1=10,combined!E124,IF(R1=11,combined!E125,IF(R1=12,combined!E126,""))))))))))))
 

Paella1

Active Member
Joined
Mar 10, 2005
Messages
382
Populate the range E114:P114 on the combined sheet with the numbers 1 to 12. Then in board uninsured cell G12 put the formula =HLOOKUP(R1,combined!$E$114:$P$115,2,FALSE).

If you can't put 1 to 12 in Row 114, use another row, but make sure it is above Row 115. Then you will need to amend the third argument in your HLOOKUP formula. For example, if you put 1 to 12 in Row 110, the formula becomes =HLOOKUP(R1,combined!$E$114:$P$115,6,FALSE).

Whichever row you put your 1 to 12 in, you might like to hide it. The formula will still work.
 

Forum statistics

Threads
1,136,352
Messages
5,675,280
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top