Excel formula for adjacent cell when adj cell is =xxx

[etherealfocus]

I've got a price sheet and a bunch of preconfigured options, and I'm tired of having to input a cell reference for both the item name and the price.

CONFIG 1
A B
item1 $33
item2 $45
item3 $11

CONFIG 2
A B
item9 $59
item7 $65
item5 $49

Etc. It's a pretty big sheet.

Right now there are just under 100 total items to select from and each of about 10 configs has 4-10 items.

What I'd like is when I choose the reference cell for a given item (=E45), the price updates automatically based on that. So I set A3 to =E45, and cell B3 becomes =E46.

I know it can be done based on reading the cell text and doing string manipulation but that seems... clunky.

Also looked into the INDIRECT and OFFSET functions, but they don't resolve the cell reference: I use OFFSET(A3,0,1) in B3 and it gives me a circular function error.

Any ideas?

 

[cbrunny]

If I understand correctly, using =OFFSET(A3,0,1) in cell B3 should give you a circular function error. This would actually give you =B3.

Are your items always sequential like that? Hard to imagine a use-case for that kind of functionality. Not sure I totally understand what you're after to be honest.

Could probably be done in a different way. Using named ranges for each of your preconfigured options, add a dropdown (data validation option I think) to select the desired preconfigured option list using vlookups.

 

[Tweak155]

Try putting this code on the worksheet (change Columns("A") to any column you'd like... I chose "A" based on your description):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns("A"), Target) Is Nothing And Target.HasFormula Then
    Dim strFunc As String
    strFunc = Target.Formula
    
    strFunc = Replace(strFunc, "=", "")
    
    Application.EnableEvents = False
    Target.Offset(0, 1).Formula = "=" & Range(strFunc).Offset(0, 1).Address
    Application.EnableEvents = True
End If
End Sub

To put the code on the sheet, hold ALT and press F11. Double click the sheet in the Project pane that you want this to work on.

 

[etherealfocus]

Here's the format:

A is label info (irrelevant)
B is first column of item names
C is first column of prices
D is 2nd column of item names
E is 2nd column of prices
F is 3rd column of item names
G is 3rd column of prices
H is empty space
I is the long list of reference item names
J is long list of corresponding prices

I'm doing =cell to put items from I into columns B, D, F and trying to get it to autofill corresponding prices from J.

cbrunny - A dropdown may be a good way to go on this... downside is the number of items in each of around 8 subcategories in the reference column keeps changing (they're just separated with an empty row) so managing the cell references would be a pain. Obvious solution is to make each subcat a column, but I have about 6 columns of product data to the right of the reference column so breaking each subcat into its own column would mean each subcat eats ~8 columns... messy, hard to work with. Is there a way to have dropdown blocks end on a blank line or similar?

Tweak - VBA isn't ideal since I have to share the sheet a lot in a restrictive security setting environment, but it's definitely better than the nonsense I'm doing now - the sheet gets adjusted several times a day on average. How does VBA work if I share the sheet with OneDrive/OD for Biz/Google Sheets?

Thanks guys

 

[Tweak155]

I'm not sure how it would translate into google sheets. I think your only other option is to set up a unique ID column (Assuming the ITEM names are not unique) and do VLOOKUPS.

 

[Tweak155]

To use the VLOOKUP based on your description:

In Column C for example (assuming your data starts on row 2 and row 1 is used for headernames everywhere):

Cell C2 function (do a drag down to complete rest):
=VLOOKUP(B2,I2:J200,2,FALSE)

 

[etherealfocus]

That did it, thanks! Still got a couple edge cases to work out but no biggie... need to have a QTY column rather than ="2x "&I15 for multiples of one item, etc.

 

[Tweak155]

Cool. J200 may need to go higher if you have more than 200 items... I just picked an arbitrary number.

 

[etherealfocus]

Yup... I've currently got around 90 items, doubt I'll ever break 200. Thanks tho