Using Text Cell Reference for Table Array

geralcde

New Member
Joined
Jan 9, 2017
Messages
1
Hello all,

I would like to ask if it is possible to type in the cell reference in a cell and use that cell for the table array?

For example

CategoryCountDateCategoryCell StartCell End
apple31/3/2017appleA1A6
apple41/4/2017PearA7A11
apple51/5/2017
apple61/6/2017
apple71/7/2017
apple81/8/2017
Pear91/3/2017
Pear101/4/2017
Pear111/5/2017
Pear121/6/2017
Pear131/7/2017

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

As you can see, apple is in column A and has the cell references spanning A1:A6, while pear has the cell references spanning A7:A11. I would like to use vlookup to search for the number of apples on 1/3/2017 and i would like to use vlookup(a1,a1:A6,2,False) for apple and then similar formula for pear but at a different cell reference of A7:A11 which can be seen on the right table. As I have many categories and dates, I cannot manually input cell references one by one, but I do have the row numbers. I cannot use vlookup for A1:A11 because it will only return the value for apple and i want for each category. How can i modify my formula in the table_array function to input the A1:A6, A7:A11 etc from my right hand table?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
Welcome to Mr Excel

I think you can simply use VLOOKUP (or SUMIFS) - don't need to input manually the ranges.
Tell us the expected/desired results for Apple and Pear.

M.
 
Last edited:

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
In addition to Marcelo's suggestion you might find that pivot tables can do much of what you need. If you're not familiar / confident with them, try here:

http://www.contextures.com/

...and if you can't get it sorted post back with examples of the kind of answers you're after...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

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